Filtered Date Range in Query
Posted on 2013-01-27
I have a form that allows the user to choose s start date and an end date and it returns the dates (via two global date variables) to build a filter string (strReportFilter) such as
"int([IN1])>=#2013/01/01# AND int([IN1])<=#2013/01/27#"
with the [IN1] parameter being a query field (date/time - general format).
This is used as a filter parameter for a report call such as...
DoCmd.OpenReport "ReportName", acViewPreview, , strReportFilter
This works just fine when the regional setting for dates is customized as a format of "yyyy/MM/dd" but does not work if another date format is in effect due to the regional settings. So, for the next example, the following formats do not work as an empty data set is returned instead of the records for the date range.
int([IN1])>=#12/12/01# AND int([IN1])<=#12/12/31#
int([IN1])>=#12-12-01# AND int([IN1])<=#12-12-31#
Now, since [IN1] is in 'general' date format, why is this happening? Both should be in the same format for comparison and there should be no problem.
When the system date format IS yyyy/MM/dd all is fine.