Choose particular month for Monthly report

[Edmee]

Hi all,

I would like to be able to select a particular month from a drop-down list or perhaps type in the name of the month (ie October) and only have records for that month displayed in a report. This isn't necessarily the current month. I thought it would be easy but it seems a bit complicated after all. Does anyone have any suggestions? Thanks very much in advance.

Edmee

 

[Maquis]

Just call your report with a criteria statement.

Dim Criteria as string
Const dquote = """" 'double quote character

Criteria = "[Monthfield] = " & dQuote & me!monthcombo & dQuote
docmd.openreport "yourreport",,,Criteria
Maq B-)
<insert witty signature here>

 

[LarryDeLaruelle]

Edmee:

I use a form with two list boxes -- one listing months and one listing years. I also have two text controls to receive the start and end dates which are used in the select query to filter the report for the date range.

The user selects the month and year for which the want the report run and then clicks on a set date command button. The dates are then created and placed in the text boxes.

Here is the code I use:

Private Sub cmdSetDate_Click()

If IsNull(lstMonth) Then
MsgBox &quot;You must select a Month&quot;, vbOKOnly + vbCritical, &quot;Day Treatment Program&quot;
lstMonth.SetFocus
Exit Sub
End If

If IsNull(lstYear) Then
MsgBox &quot;You must select a Year&quot;, vbOKOnly + vbCritical, &quot;Day Treatment Program&quot;
lstYear.SetFocus
Exit Sub
End If

Dim strDate As String
Dim datStart As Date

strDate = lstMonth & &quot;/01/&quot; & lstYear
datStart = strDate
txtStart = datStart
txtEnd = DateAdd(&quot;m&quot;, 1, txtStart)
txtEnd = DateAdd(&quot;d&quot;, -1, txtEnd)

End Sub

The list box for month lists the literal value (i.e., January, February, etc); ditto for the year list box.

The code creates the begin date by concatenating the selected month with the first day of the month and the selected year. I then add one month to that date to get the first day of the next month and then step back one day to get the last day of the selected month.

I found that I first have to put the date into a string type variable and then assign it to a date type to make this work.

This can be easily modified to allow the user to specify a number of months, select by quarters, etc.

Hope this helps. Let me know if you have any questions.
Larry De Laruelle
larry1de@yahoo.com