Combo box with date periods to filter report (1 Viewer)

[gojets1721]

So I have a form that I use to filter a report. It works well and I have two date fields to let the user filter between two dates. I would also really like to include (if possible) a combo box which has preset date filters that the user could select instead of using the date pickers (i.e. "year to date", "last quarter", "this month", etc.)

I couldn't find any guides online for this. Any suggestions?

 

[arnelgp]

depends on "How" you calculate the "dates" between those combo items.
usually you pass it to a function/sub then use Case (or If) to Build whichever filter
for your data/table/query.

 

[CJ_London]

You could use a valuelist for your combo, 2 columns , ‘year to date’ etc for the first column and the second the filter string required for the filter. Probably need code in the form open event to calculate the latter since it could change from day to day

 

[moke123]

as arnel suggests, something like this

 

[LarryE]

Assume you have a date field in your reports Record Source named [BalanceDate] and a report named MyReport.
For dates this month you could use:
DoCmd.OpenReport "MyReport", acViewPreview, , "[BalanceDate]>= Month(Now) & '/01/' & Year(Now)", acWindowNormal
For this year:
DoCmd.OpenReport "MyReport, acViewPreview, , "[BalanceDate]>= '01/01/' & Year(Now)", acWindowNormal
For 1st quarter:
DoCmd.OpenReport "MyReport", acViewPreview, , "[BalanceDate]>= '1/01/' & Year(Now) And [BalanceDate]<=3/31/' & Year(Now)", acWindowNormal

 

[moke123]

"[BalanceDate]>= Month(Now) & '/01/' & Year(Now)",

I would expect that to fail as dates need to be delimited with octothorpes so they are recognized as dates. ( ie. #12/25/22# )
Rather than concatenating the date, I would use DateSerial. the first day of a month would be dateSerial(Year(date),Month(date),1)
To get the last date of a month you would use DateSerial for the ZERO day of the following month . This would make up for months that have 30 days instead of 31 or for leap years. ie DateSerial(year(date),Month(date)+1, 0)

Private Sub lstDates_AfterUpdate()

    Select Case Me.lstDates

        Case 1 'yr to date

            Me.dteStart = DateSerial(Year(Date), 1, 1)
            Me.dteEnd = Date

        Case 2 'current month

            Me.dteStart = DateSerial(Year(Date), Month(Date), 1)
            Me.dteEnd = DateSerial(Year(Date), Month(Date) + 1, 0)

        Case 3 ' last month

            Me.dteStart = DateSerial(Year(Date), Month(Date) - 1, 1)
            Me.dteEnd = DateSerial(Year(Date), Month(Date), 0)

        Case 4 ' 1st quarter

            Me.dteStart = DateSerial(Year(Date), 1, 1)
            Me.dteEnd = DateSerial(Year(Date), 4, 0)

        Case 5 ' 2nd quarter

            Me.dteStart = DateSerial(Year(Date), 4, 1)
            Me.dteEnd = DateSerial(Year(Date), 7, 0)

        Case 6 ' 3rd quarter

            Me.dteStart = DateSerial(Year(Date), 7, 1)
            Me.dteEnd = DateSerial(Year(Date), 10, 0)

        Case 7 ' 4thquarter

            Me.dteStart = DateSerial(Year(Date), 10, 1)
            Me.dteEnd = DateSerial(Year(Date) + 1, 1, 0)

    End Select

End Sub

 

[LarryE]

I would expect that to fail as dates need to be delimited with octothorpes so they are recognized as dates. ( ie. #12/25/22# )
Rather than concatenating the date, I would use DateSerial. the first day of a month would be dateSerial(Year(date),Month(date),1)
To get the last date of a month you would use DateSerial for the ZERO day of the following month . This would make up for months that have 30 days instead of 31 or for leap years. ie DateSerial(year(date),Month(date)+1, 0)

Private Sub lstDates_AfterUpdate()

    Select Case Me.lstDates

        Case 1 'yr to date

            Me.dteStart = DateSerial(Year(Date), 1, 1)
            Me.dteEnd = Date

        Case 2 'current month

            Me.dteStart = DateSerial(Year(Date), Month(Date), 1)
            Me.dteEnd = DateSerial(Year(Date), Month(Date) + 1, 0)

        Case 3 ' last month

            Me.dteStart = DateSerial(Year(Date), Month(Date) - 1, 1)
            Me.dteEnd = DateSerial(Year(Date), Month(Date), 0)

        Case 4 ' 1st quarter

            Me.dteStart = DateSerial(Year(Date), 1, 1)
            Me.dteEnd = DateSerial(Year(Date), 4, 0)

        Case 5 ' 2nd quarter

            Me.dteStart = DateSerial(Year(Date), 4, 1)
            Me.dteEnd = DateSerial(Year(Date), 7, 0)

        Case 6 ' 3rd quarter

            Me.dteStart = DateSerial(Year(Date), 7, 1)
            Me.dteEnd = DateSerial(Year(Date), 10, 0)

        Case 7 ' 4thquarter

            Me.dteStart = DateSerial(Year(Date), 10, 1)
            Me.dteEnd = DateSerial(Year(Date) + 1, 1, 0)

    End Select

End Sub

View attachment 103543

You might expect it to, but doesn't. Have been using this as criteria for a long long time.

 

[moke123]

You might expect it to, but doesn't. Have been using this as criteria for a long long time.

I am surprised although your 1st quarter example fails with a datatype mismatch.

 

[LarryE]

I am surprised although your 1st quarter example fails with a datatype mismatch.

Sorry, I missed a ' after the <=
DoCmd.OpenReport "MyReport", acViewPreview, , "[BalanceDate]>= '1/01/' & Year(Now) And [BalanceDate]<='3/31/' & Year(Now)", acWindowNormal

 

[gojets1721]

I would expect that to fail as dates need to be delimited with octothorpes so they are recognized as dates. ( ie. #12/25/22# )
Rather than concatenating the date, I would use DateSerial. the first day of a month would be dateSerial(Year(date),Month(date),1)
To get the last date of a month you would use DateSerial for the ZERO day of the following month . This would make up for months that have 30 days instead of 31 or for leap years. ie DateSerial(year(date),Month(date)+1, 0)

Private Sub lstDates_AfterUpdate()

    Select Case Me.lstDates

        Case 1 'yr to date

            Me.dteStart = DateSerial(Year(Date), 1, 1)
            Me.dteEnd = Date

        Case 2 'current month

            Me.dteStart = DateSerial(Year(Date), Month(Date), 1)
            Me.dteEnd = DateSerial(Year(Date), Month(Date) + 1, 0)

        Case 3 ' last month

            Me.dteStart = DateSerial(Year(Date), Month(Date) - 1, 1)
            Me.dteEnd = DateSerial(Year(Date), Month(Date), 0)

        Case 4 ' 1st quarter

            Me.dteStart = DateSerial(Year(Date), 1, 1)
            Me.dteEnd = DateSerial(Year(Date), 4, 0)

        Case 5 ' 2nd quarter

            Me.dteStart = DateSerial(Year(Date), 4, 1)
            Me.dteEnd = DateSerial(Year(Date), 7, 0)

        Case 6 ' 3rd quarter

            Me.dteStart = DateSerial(Year(Date), 7, 1)
            Me.dteEnd = DateSerial(Year(Date), 10, 0)

        Case 7 ' 4thquarter

            Me.dteStart = DateSerial(Year(Date), 10, 1)
            Me.dteEnd = DateSerial(Year(Date) + 1, 1, 0)

    End Select

End Sub

View attachment 103543

This worked!!Thanks so much!!