Date problem

[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.

 

[Allen Browne]

In the Criteria row in query design under your date field, enter:

 

[John Spencer]

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()

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

 

[julief]

I have tried this and it still only bring back the records which do not have
the time stamp.

 

[julief]

I have tried this and it still only brings back record with no time stamp.

 

[John Spencer]

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

 

[julief]

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.

 

[Allen Browne]

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.