I have a report with multiple subreports. Some subreports require input of one date [Month] and others require input of a date range [IncidentMonth]. I am using a form to enter the date range [FromDate] and [ToDate]. Can someone please help with the VBA code ... what I am using doesn't work.

DoCmd.OpenReport stDocName, acPreview, , "[IncidentMonth] Between Forms![frmRunReport]![FromDate] And Forms![frmRunReport]![ToDate] AND [Month] = Forms![frmRunReport]![FromDate]"

Thanks

Hi David,

You need to seperate the variables from the String, as shown below (kep points in bold)
    Note, I have also formatted the dates to UDF format, and placed them in hashes (#) which is how Access recognises them as dates.

I hope this helps,

Rob

Not quite, Rob. Formatting is used only for the purposes of displaying something on a form or report. It is not used as a criteria for a query or as a WHERE condition.

Also, the date range is supposed to be a date, not just a month. Assuming [IncidentMonth] is a full date, then you simply need

DoCmd.OpenReport stDocName, acPreview, , _
    "[IncidentMonth] Between #" & Forms.frmRunReport.Form.FromDate & "# And #" & Forms.frmRunReport.Form.ToDate & "#"

Or if the code is already being called from frmRunReport, then

DoCmd.OpenReport stDocName, acPreview, , _
    "[IncidentMonth] Between #" & Me.FromDate & "# And #" & Me.ToDate & "#"

If IncidentMonth is truly a month and not a full date, then you need to do something like this, keeping in mind that the month is an integer (no # needed).

DoCmd.OpenReport stDocName, acPreview, , _
    "[IncidentMonth] Between " & Month(Me.FromDate) & " And " & Month(Me.ToDate)

Month() is a built-in Access function and should not be used as a field or variable name.

 Why is this? UI elements often need to be reformatted, Access always stores data in MM/DD/YYYY format, so I was told its always a good idea to always ensure you format SQL strings, especially when working with dates.

Good Spot! I missed the fact that it was only a month!!

Regards,
Rob

It's one thing to reformat to display something or to find a criteria for a string, but not for data types of DATE.

A date is a date is a date to the computer... no matter how you format it. So that's why I said that when I saw that the criteria was a date.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Not when the information being passed is a String.

Everyone (in the geeky world!) knows that dates are stored as numbers, hence the "date is a date is a date".
But SQL can only work against a text language, hence the formatting of the date is important.
The string has to be parsed into date serial by the data provider, get your month and the day around the wrong way, you've got problems...

Yes, IF the control formatting is set to the correct date format,
and the format is set correctly in the table design view, it will
work ok. But, its best practice to assume nothing with things like
this, and it has rapidly become the de facto standard to ALWAYS
work with dates in UDF format, Access and SQL Server (or most other
modern data providers) will always treat this String the same way.

I'm not trying to flame, but although what you are saying is right,
(as in dates are stored as serials), Access tries to be "smart" and
you can get some odd goings on with dates (you may not experience it
so much in the States). But, WHERE clauses are not "dates" they are
Strings, Strings must be parsed, and in order to parse correctly,
an expected format should be used.

Regards,
Rob

Gentlemen,
Thanks for your responses.
If I leave out the reference to [Month] and concentrate on (my original) where [IncidentMonth] is a date -
DoCmd.OpenReport stDocName, acPreview, , "[IncidentMonth] Between Forms![frmRunReport]![FromDate] And Forms![frmRunReport]![ToDate]"
I get a report with a page for each month (12 pages for a year).
If I use Rob's suggestion -
DoCmd.OpenReport _
    stDocName, acPreview, , _
    "[IncidentMonth] Between #" & Format$(Forms![frmRunReport]![FromDate], "yyyy-mm-dd") & "# And #" & Format$(Forms![frmRunReport]![ToDate], "yyyy-mm-dd") & "#"
I get exactly the same result.
If I use Serrano's suggestion -
DoCmd.OpenReport stDocName, acPreview, , _
    "[IncidentMonth] Between #" & Forms.frmRunReport.Form.FromDate & "# And #" & Forms.frmRunReport.Form.ToDate & "#"
I get a report with 8 pages ... the last 4 pages don't print?????????
If I use the criteria for [IncidentMonth] Between [FromDate] And [ToDate] in each query and manually insert the values at report run time I get a one page report which is what I want. Why the difference?

First I have to ask what the field IncidentMonth actually is. Is it a full date or is it just a month?


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Been away for a few days, hence the pause.

IncidentMonth is a date. I used a full date for simplicity, always the first day of the month.

The code I gave you should work if the FROM and TO dates are correct. Something else is going on that I cannot see just from these messages. Perhaps if you posted the code for the PRINT button's ON CLICK event and you explained the source of the report and any groupings it has.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division