Date/Time Search Midnight Issue

[Coffee]

hello, i am using the between statements to search between two date periods and then between specific times on those dates. if i search with the following criteria:

Start Date = 11/07/2011, End Date = 12/07/2011
Start Time = 11:55:00 PM, End Time = 12:05:00 AM

it returns wrong results. i think this is due to going over midnight to the next day so it searches between times 12.05AM to 11:55PM on a single day instead of 11.55PM to 12.05AM. Does anyone know how to get it to search past midnight? SQL code used is below.

Thanks

Text36 = start date
Text38 = finish date
Text9 = start time


Text11 = finish time

Code:

SELECT DISTINCT Historical_Data.MessageNo, Historical_Data.DateTime, Historical_Data.UserName, Historical_Data.FCS, Historical_Data.KKSCode, Historical_Data.Message

FROM Historical_Data

WHERE DateValue((Historical_Data.DateTime) BETWEEN Forms!Main_Form!Text36 AND Forms!Main_Form!Text38 AND TimeValue(Historical_Data.DateTime) BETWEEN Forms!Main_Form!Text9 AND Forms!Main_Form!Text11));

[June7]

12:05 am would not be 12/07/2011, it is 12/08/2011.

[Coffee]

hey, Oh sorry i forgot to say my date format is DD/MM/YYYY, not MM/DD/YYYY. so im still having this issue :S any ideas?

[June7]

I did some testing with the MM/DD/YYYY structure and that works. Is your date data actually stored as DD/MM/YYYY or is this just a display formatting?

[Coffee]

hi,

the date format is actually imported and stored in the database as DD/MM/YYYY, so no its not a display format. however it is a date/time field so the complete layout of the field data is "DD/MM/YYYY HH:MM:SS AM/PM". do you think this could be the issue?

thanks for your response

[June7]

That can be a problem.

Google: Access dd/mm/yyyy

Here is one http://databases.aspfaq.com/general/...d-for-all.html