capture dates by quarters in access

Question

To make my life much easier working with my data I set 3 columns of same date content. The first displays in the format mm/dd/yyyy, second in the format of yyyy-mm and the third in the format yyyy-q. I did it purposely due to my reports. Sometimes I need to create monthly, quarterly, yearly etc. Usually I work with a form where I invite the user select start and end date and by a click of a button run a report. This report extracts a query where I specify on the date section to pull all information between start and end date. This time I want to do the same procedure but instead of start and end date - I want the user to select which quarter he wants so that the query will pull all information regarding this quarter. What do I specify in the criteria to archive this?

Gustav Gustav · Accepted Answer · 2015-08-25T15:42:09

Filter on

DatePart("q", [YourDateField])

or

Format([YourDateField], "yyyyq")

To obtain the first and last date of a quarter, given the year and the quarter, you can use these expressions:

DateQuarterFirst = DateSerial(Y, 1 + 3 * (Q - 1), 1)
DateQuarterLast = DateSerial(Y, 1 + 3 * Q, 0)

If you have a date of the quarter, you can these functions to obtain the first and last date of the quarter of that date:

Public Function DateThisQuarterFirst( _
    Optional ByVal datDateThisQuarter As Date) As Date

    Const cintQuarterMonthCount   As Integer = 3

    Dim intThisMonth              As Integer

    If datDateThisQuarter = 0 Then
        datDateThisQuarter = Date
    End If
    intThisMonth = (DatePart("q", datDateThisQuarter) - 1) * cintQuarterMonthCount

    DateThisQuarterFirst = DateSerial(Year(datDateThisQuarter), intThisMonth + 1, 1)

End Function


Public Function DateThisQuarterLast( _
    Optional ByVal datDateThisQuarter As Date) As Date

    Const cintQuarterMonthCount   As Integer = 3

    Dim intThisMonth              As Integer

    If datDateThisQuarter = 0 Then
        datDateThisQuarter = Date
    End If
    intThisMonth = DatePart("q", datDateThisQuarter) * cintQuarterMonthCount

    DateThisQuarterLast = DateSerial(Year(datDateThisQuarter), intThisMonth + 1, 0)

End Function

capture dates by quarters in access

1 Answers