dates not filtering

[RSXchin]

I have a field called HistoryTimestamp that is a date column and has dates written like "7/20/2011 7:00:00 AM" (2 spaces)

I tried

Select * from HistoryTable
WHERE HistoryTimestamp > 7/20/2011

But that returns ALL records. any suggestions?

 

[Joe4]

That's because dates are stored in Access/Excel as the number or days since 1/1/1900.

And 7/20/2011, as you have written it, means "7 divided by 20 divided by 2011", which will be some very small fraction less than one, so all days after 1/1/1900 will be returned.

To indicate dates in Access, you need to surround them by the pound sign, i.e.
WHERE HistoryTimestamp > #7/20/2011#

 

[RSXchin]

You're amazing!!!

 

[RSXchin]

How could I ask it to do just the ones for today?

 

[Joe4]

If you leave the time component off of your date, it defaults to midnight (12:00 AM). So one way is to do it like this:

WHERE HistoryTimestamp BETWEEN #7/20/2011# AND #7/21/2011#

 

[Norie]

RSXchin

So you managed to get the data into Access?

 

[Joe4]

The following link has a lot of good information for creating criteria for Date/Time fields, including dynamically selecting the current date, instead of hard-coding it: http://office.microsoft.com/en-us/access-help/examples-of-query-criteria-HA010066611.aspx

 

[RSXchin]

RSXchin

So you managed to get the data into Access?

No, but excel has SQL functions