Problem with setting criteria on datetime value (1 Viewer)

[LemonTwist]

I am trying to add criteria to a date column on my Access query so that it only returns records from between 45 days ago and today's date. In the Criteria box for the date column I have put

Between Date()-45 And Date()

but when I try to run the query, I get an error message saying "ODBC Call Failed. The String representation of a datetime value is not a valid datetime value".

I have checked that the relevant column in the table I'm querying is in Date/Time format, and also the column in the table to which I'm appending the data is in Date/Time format.

Does anyone know what could be causing this error, and what I can do to fix it?

 

[Starman]

Try

Between DateAdd("d",-45,Date()) And Date())

 

[LemonTwist]

I've just tried that and unfortunately I still get the same error, but thanks for the suggestion anyway.
Funnily enough, I just spoke to a co-worker and when he runs the exact same query every morning, he doesn't get the error message and the data still turns out correct in the end. Access is bizarre sometimes; I can't get my head around this.