Strange Results Returned Dates

[supermaestro]

The query below works. It takes 4 values from a form (two of them short dates). The search looks for a transaction date between the Date From and Date To. If a transaction date is '30/05/2003' and a user enters Date From - '01/05/2003' and Date To - '11/06/2003' then results are returned successfully. However, if a user enters a Date To of anything between 01/06/2003 or 01/05/2003 then no results are returned. There appears to be a a problem calculating the dates when using days of 01, 02, 03, 04, or 05. Any ideas?

rst.Open "SELECT * FROM contacts, transactions WHERE contacts.[Contact ID] = transactions.[Contact ID] And (contacts.[Account Type] = '" & [Forms]![Report Form Options]![Account Type] & "') And (transactions.[Last Transaction] = '" & [Forms]![Report Form Options]![Transaction Type] & "') And (transactions.[Transaction Date] BETWEEN #" & Format([Forms]![Report Form Options]![Transaction Date From], "dd/mm/yyyy&quot & "# AND #" & Format([Forms]![Report Form Options]![Transaction Date To], "dd/mm/yyyy&quot & "#);"

 

[DanChard]

Virtually all unexpected date problems are due to the difference in US and British date formats (ie, 2nd July 1980 [my birthday] would be 02/07/1980 in the British format but 07/02/1980 in the US format).

Therefore, you putting a date range of between 01/05/2003 and 11/06/2003 would probably evaluate to anything between 5th January 2003 and 6th November 2003! Putting 06/01/2003 or 05/01/2003 as the to date would evaluate to either the 1st of June 2003 or the 1st May 2003. The end result of this is a date range of 5th January 2003 to either 1st June 2003 or 1st May 2003.

Try changing the occurences of dd/mm/yyyy in your query to mm/dd/yyyy and see what happens.

Hope this is of some use...