Reports filtered by Month

[Douglasrac]

Hi,

I want a report that show data only for this month, not all data available on the database, and I can´t figure out how to do it.

I have a field with data in the format DD/MM/YYYY. The report should show data from this month, or even better, a combo box where I can choose which month to show.

I also tried filters, but didn´t work.

[banjo1t]

Have you tried.....

Events.[example] Between #1/1/2011# And #6/31/2011#

You will need to replace Events & example with your record souce.....

[Rawb]

There are a couple different ways of doing this, but the best way (imo) is to do the following:

• Create a Query for the Report (or just use the Table if that has all the data you need for the Report).
• Create a Form with your date selection combo box and a "View Report" button.
• Create your Report so that it uses the Table/Query. At this point, it will show all Records, not just those for your specific date. This is fine because we'll be fixing that later.
• Set the Report's "Filter On" property to Yes. Don't worry about a specific Filter, that will be done elsewhere.
• Go back to the Form and set the following VBA Code as your Command Button's "On Click" event:

  Dim dteStart, dteEnd as Date
  Dim strFilter as String

  dteStart = DatePart("m", Me!FormDate) & "/1/" & DatePart("yyyy", Me!FormDate)
  dteEnd = DateAdd("d", -1", DateAdd("m", 1, dteStart))

  strFilter = "[DateField] Between #" & dteStart & "# And #" & dteEnd & "#"

  DoCmd.OpenReport, "MyReportName", acViewPreview, , strFilter

Just be sure to change FormDate to the name of your date combo box, DateField to the name of the field the dates are stored in, and MyReportName to the name of your Report.

What the VBA code does is create two dates based off your combo box's date. The first one is the date of the first day of the month, and the second is the last day of the month. Then it Filters your Report to anything between those two dates.

[Douglasrac]

Thanks for all the help.

I figure out that the easiest way is just to change the month directly on the Criteria of the Query.