Date Query Criteria

[sherlocked]

Hello experts,

I have a query I'm building that searches for records created today. I have the below in my query that extracts the date portion of the date/time field called "CREATED" that shows when the record was entered in the DB:

TheDate: DateValue([Created])

I'm trying to search on this field by putting "=Date()" as my query criteria, but I get an "expression is too complex to be evaluated" message when I try to run the query.

Any thoughts on how I can accomplish what I'm trying to do? I just need to run a query showing me records created that day for a specific employee.

Thanks in advance!

 

[Minty]

In the criteria section put in >=date() and < date() +1 which will accommodate the time portion of your fields values.

 

[plog]

Do you have any other criteria? And is [Created] an actual Date/Time data type in the underlying table? It may contain what looks like date/time, but is it really Short Text instead?

And, are there any Null values in [Created]?

 

[Ranman256]

instead of DateValue(created), try Format([created], "mm/dd/yyyy")
put DATE() in the criteria field

or if that fails, make 2 queries,
Q1, using Format([created], "mm/dd/yyyy"), no criteria
Q2 using Q1 then set the critiera here.

 

[almahmood]

Hi,

DateValue returns '#Error' with null value. To avoid null value you can write your query like DateValue(CDate(Nz([CreatedDate],0)))=Date() and this works fine.

 

[sherlocked]

Thank you for these suggestions, the only one that worked is the >=date() and < date() +1 but this only works once. This query is behind a subform that re-queries when the user clicks a button to submit a record. It works fine the first time, then won't work again. I can't figure out why

CREATED is a Date/Time field in the table design.

There should not be null values in the CREATED field as it auto-populates when the user clicks submit.

 

[sherlocked]

I believe I figured this out. My query had other criteria that was preventing the new records from appearing in my subform when it was re-queried. Looks like I am in business now - the >=date() and < date() +1 in the CREATED field worked.

Thank you