date format issue 1

[deedar]

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

 

[dhookom]

All SQL queries expect the m/d/y format or similar. You can't use d/m/y. Try format your dates in your strFilter using the format function to get m/d/y or d-mmm-y or other format that SQL will understand. There are a number of better discussions of this on the web.

Duane
Hook'D on Access
MS Access MVP

 

[deedar]

Thank you.

I formatted dates in strFilter using the format function:

strFilter = "[f3] Between #" & Format(Me.Combo11, "m/d/yyyy") _
         & "# and #" & Format(Me.Combo13, "m/d/yyyy") & "#"

and it worked correctly.