Date Format

[Guest]

I have inherited a data base which has an option for filtering records by
start and end date. The problem is if the date is say 11/04/2007, ie Day,
Month, Year the filter returns values of 04/11/2007. If the date is say
22/04/2007, it returns the correct sequence. The following code is used in
the filter:
=Choose([grpFilterOptions],"[TransactionDate] Between #" & Forms![View
Reports].[Beginning Trans Date] & "# AND #" & Forms![View Reports].[Ending
Trans Date] & "#","")
Any help would be appreciated.

 

[Allen Browne]

Try:
=Choose([grpFilterOptions],"[TransactionDate] Between #" &
Format(Forms![View Reports].[Beginning Trans Date], "mm\/dd\/yyyy") &
"# AND #" &
Format(Forms![View Reports].[Ending Trans Date], "mm\/dd\/yyyy") & "#","")

For an explanation, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html