Query criteria Date() not accurate for fields set as General Date (date/time)

[isaacski]

Hi All,
I have a query set up to pull records from a table where the date is today and the brand=x. I recently changed the date format in the table from Short date to General Date so it will record the Date AND time that the record is added.

My query doesn't seem to be capturing all of the records added on today's date. Is there another criteria to capture today's date that will work with date/time?

Thanks!

 

[rzw0wr]

Date() function does not store the time, just stores the date.
Now() stores all.

Data() should work in your query.

Depending on how you query is written you may need to delimit the dates.
# is the date delimiter.

If you still have problems please paste your SQL.

Dale

 

[Brianwarnock]

As you are now storing the time you have to allow for that in your criteria, however if you are only filtering by date then use DateValue to just extract the date part of your field.


## is only required round date strings , not needed when referencing fields or controls containing dates.

Brian

 

[Pat Hartman]

Format has nothing to do with what is stored. Format only impacts what is displayed. As the others have mentioned, you would use Date() or Now() depending on whether you wanted to save only the date or both the date + the time.

When creating a query that will select data based on a date or a date range from a colum that contains a time component, you need to use the DateValue() function to extract only the date from the DateTime field. You would use TimeValue() if you only wanted the time.

Where DateValue(YourDateField) = #05/24/2013#;