Query Date field Criteria question. Simple I'm sure. Ver 2002/2003

[Edward_]

I have a Time_On field where time/date is stored is this format: 5/21/2015 11:01:01 AM

I'm trying to create a query, with the Criteria: Like "5/21/2015", (under 'Time_On') no records are found (when multiples of this date exists).

So I created an expression in the query like this: Date: Format([Time_On],"Short Date"). This produces a nice field/column of date-only (ie. 5/21/2015). All ok.



When I place a Like "5/21/2015" in the Critera of the Expression I get results.


What syntax would I use to avoid the expression, and do the short date format in my criteria cell of Time_On? Is this possible?

Any advice appreciated.

[pbaldy]

I think the way you're doing it is fine. You can also use the DateValue() function in a new field, or on your actual field a Between that uses the date alone and the date plus 11:59:59 PM (you can use the DateAdd() function to get there).

[Edward_]

Great, thanks pbaldy, I'll stay with the expression.

By the way, could you suggest Criteria syntax for backdating one day?
I'm testing this: DateAdd("d",-1,"Date") and Like "DateAdd("d",-1,"Date")". But I'm getting a Data type mismatch on these.

I'll be using the SQL string from this in an automated VB application.

[pbaldy]

If you mean from "today", you don't want quotes around the Date() function:

DateAdd("d",-1,Date())

Access may remove the parentheses after Date, which is fine.

[Edward_]

That solved it!!
Thanks for your time.

Access left it as: DateAdd("d",-1,Date())

[pbaldy]

Happy to help!