Report by Month

[bhipwell via AccessMonster.com]

Hello,

I have a report for which I want the user to filter for any particular month
within the last 12 months. For example, a drop down box would appear with
the 12 months (January, February, etc.). When the user selects February, the
report will filter for all records for February of the current year (or
December of last year if today is October 2nd).

Help...!

BH

 

[Duane Hookom]

Assuming you have a form , frmRptCrit, with a combo box named cboMonth. You
could add code to the Open event of the form of:

Private Sub Form_Open(Cancel As Integer)
Dim cbo As ComboBox
Dim intMth As Integer
Dim datMonth As Date
Dim strRowSource As String
Set cbo = Me.cboMonth
cbo.RowSourceType = "Value List"
cbo.ColumnCount = 2
cbo.BoundColumn = 1
cbo.ColumnWidths = "0;1"
cbo.ListRows = 12
datMonth = DateSerial(Year(Date), Month(Date), 1)
For intMth = 0 To 11
strRowSource = strRowSource & Format(datMonth, "yyyymm") & ";" &
Format(datMonth, "mmmm") & ";"
datMonth = DateAdd("m", -1, datMonth)
Next
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
cbo.RowSource = strRowSource
End Sub

The in your report's record source query, you can use a criteria like:
WHERE Format([DateField],"YYYYMM") = Forms!frmRptCrit!cboMonth