Changed Date in Query

[Peter]

Hi again,

Since i changed my DateModified date format from dd-ddd-mmm-yyyy to General
Date (this since the date is enterd date and time) my query will not retrieve
any data when the criteria is set to Date() in the DateModified. Since i
changed the format in this field...some dates are in the old format...

Well..did i explain it well?

 

[Marshall Barton]

Since i changed my DateModified date format from dd-ddd-mmm-yyyy to General
Date (this since the date is enterd date and time) my query will not retrieve
any data when the criteria is set to Date() in the DateModified. Since i
changed the format in this field...some dates are in the old format...

You locked onto the wrong issue. The Format has nothing to
do with the value or how it compares to other values.

The problem here is that you emtered values that include the
time part as well as the date. A date value (obtained via
the Date() function) includes a time part of midnight which
will not be equal to a date eith a time part for any other
time of the day.

Try changing the criteria from Date() to

 

[John Spencer]

Is the field DateModified a date field? If so the format will affect
the display only.

If the DateModified contains a date and a time, then checking for date
will get you only those records that were modified at exactly midnight
on the current date. You probably want to find records that were
modified between midnight today and midnight of tomorrow.

Criteria of
Between Date() and DateAdd("d",1,Date())
will get all records where DateModified is between midnight and midnight
inclusive. If getting a record (or records) that were modified at
exactly midnight of the next day causes a problem then use criteria like

=Date and < DateAdd("d",1,Date())

If the field DateModified is a text field, then further changes need to
be made.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Peter]

Thanks you very much Marshall! Being quit ignorent in these matters..this
would have taken a life time to figure out by my self. Once again, Thanks a
lot!