I am having problem in passing WhereCondition in Docmd.OpenReport:
DoCmd.OpenReport stDocName, acPreview, , strFilter
The issue is with the DateFormat.
I created a sample database to explain my issue. Selection Form of report has a variable strFilter and is assigned the following:
strFilter = "[f3] Between #" & Me.Combo11 _
& "# and #" & Me.Combo13 & "#"
f3 is a Date/Time field in a table t1.
t1 has the following sample values for f3:
1/1/2009 (1st Jan)
1/2/2009 (1st Feb)
1/3/2009 (1st Mar)
1/4/2009 (1st Apr)
1/5/2009 (1st May)
I selected 1/1/2009 in Combo11 and 1/5/2009 in Combo13 and watched the value of strFilter in the immediate window, which showed the following:
[f3] Between #1/1/2009# and #1/5/2009#
But when the report displayed, it only showed the first record having date 1/1/2009.
I checked Regional Options of my computer and Short date format is d/M/yyyy.
I created a sample query, selected fields f1, f2 and f3 of table t1 and in Criteria for f3, I typed the following:
Between 1/1/2009 and 1/5/2009
Upon clicking TAB, it changed to:
Between #1/1/2009# And #1/5/2009#
When I viewed the SQL View, Query was as follows:
SELECT t1.f1, t1.f2, t1.f3
FROM t1
WHERE (((t1.f3) Between #1/1/2009# And #5/1/2009#));
Strangely, 1/5/2009 was changed to 5/1/2009 and the query was working only with 5/1/2009 showing all 5 records. When I changed 5/1/2009 to 1/5/2009 in SQL View, again it showed only 1 record having the date 1/1/2009.
How can I maintain the date time format to d/m/yyyy at the same time achieving results in SQL Query without changing the format to m/d/yyyy
DoCmd.OpenReport stDocName, acPreview, , strFilter
The issue is with the DateFormat.
I created a sample database to explain my issue. Selection Form of report has a variable strFilter and is assigned the following:
strFilter = "[f3] Between #" & Me.Combo11 _
& "# and #" & Me.Combo13 & "#"
f3 is a Date/Time field in a table t1.
t1 has the following sample values for f3:
1/1/2009 (1st Jan)
1/2/2009 (1st Feb)
1/3/2009 (1st Mar)
1/4/2009 (1st Apr)
1/5/2009 (1st May)
I selected 1/1/2009 in Combo11 and 1/5/2009 in Combo13 and watched the value of strFilter in the immediate window, which showed the following:
[f3] Between #1/1/2009# and #1/5/2009#
But when the report displayed, it only showed the first record having date 1/1/2009.
I checked Regional Options of my computer and Short date format is d/M/yyyy.
I created a sample query, selected fields f1, f2 and f3 of table t1 and in Criteria for f3, I typed the following:
Between 1/1/2009 and 1/5/2009
Upon clicking TAB, it changed to:
Between #1/1/2009# And #1/5/2009#
When I viewed the SQL View, Query was as follows:
SELECT t1.f1, t1.f2, t1.f3
FROM t1
WHERE (((t1.f3) Between #1/1/2009# And #5/1/2009#));
Strangely, 1/5/2009 was changed to 5/1/2009 and the query was working only with 5/1/2009 showing all 5 records. When I changed 5/1/2009 to 1/5/2009 in SQL View, again it showed only 1 record having the date 1/1/2009.
How can I maintain the date time format to d/m/yyyy at the same time achieving results in SQL Query without changing the format to m/d/yyyy