Date problem

  • Thread starter Thread starter julief
  • Start date Start date
J

julief

I have a table into which I import a date field, this is set to short date in
the property but does import in ddmmyy:hhmm format, some records import with
the time on others dont.

I wish to put a query together which pulls through all records with
yesterdays date on.

I have written the query which will pull through the records from yesterday
without the time stamp, but no matter what I do I cannot get to include the
records with the time stamp on them.

Any pointers would be most welcome.
 
It would help if you posted the SQL statement that you are using. It is
difficult to trouble-shoot what one cannot see.

Have you tried criteria that looks like
= DateAdd("d",-1,Date()) and < Date()
Click to expand...

That should retrieve all records that have yesterday's date including those
with a time.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
I have tried this and it still only bring back the records which do not have
the time stamp.
 
As a guess the field is not a date field, but is a text field that contains a
date string. Are you sure about the data type?

You might try the following criteria
LIKE DateAdd("d",-1,Date()) & "*"

Or use

DateValue(TheDateField) = DateAdd("d",-1,Date())

Again, you have not posted the SQL statement you are using. Hint: View: SQL
from the menu.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
As far as I can see it is a date field. The import file is a txt file, the
table it is imported to has the properties set to short date.
I have tried you suggestions as below, but still with no luck. Have you any
other suggestions or advise as to how I can check the field.
 
Something else is going on then.

If you have a date/time field with a time component, greater than or equal
to yesterday midnight, and less than today does include all times yesterday.
 
Back
Top