Query Date Format Issue (1 Viewer)

Hi everyone,
Please see attached screenshot of my query problem.

I have a query that looks at a table to get all values between midnight and 6:00am for the current date. The query works fine until the date changes to a single digit.

I can change A: Format([DateAndTime],"dd/mm/yyyy") to
A: Format([DateAndTime],"d/mm/yyyy") and the query will work.
However when the date changes to double digits it stops working again.

Does anyone know of a solution for this

Thanks

So in other words, they are not really Date and Time type but simply Text type. Try casting the text to a proper date format, using the CDate function.

You are changing your date field to a text field and try and put a where clause on that which is a BAD idea for multiple reasons but mostly because of the problems you are experiencing

Best solution is to remove both where clauses and replace the
<Date & " 5:59:59"
by
Between date() and date() + timevalue("5:59:59")

A date is a date for a reason, medling with its format is asking for trouble.
Moreover implicit conversions, like you are doing with Date & " 5:59:59" is asking for access to do the thinking/guessing for you... which again is a bad idea. Explicit conversion by in this case using Timevalue work much more consistant.

Thanks Guys

Using "Between date() and date() + timevalue("5:59:59")" was a great improvement. Seems to work a lot quicker too.