Criteria help

[Matthew Dyer]

I am trying to set my criteria to pull a specific date for my range of
data. =#11/1/2010# would work but the data is stored in long date
format (date, time, etc.). What would I use for my criteria to pull
anything from 11/1/10?

 

[John Spencer]

=#11/1/2010# AND <#11/2/2010#

Or slower but it will work. Use a calculated field to return just the date
portion.
Field: DateValue(TheField)
Criteria: = #11/1/2010#

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

 

[Bob Barrows]

I am trying to set my criteria to pull a specific date for my range of
data. =#11/1/2010# would work but the data is stored in long date
format (date, time, etc.). What would I use for my criteria to pull
anything from 11/1/10?

Just to expand on John's answer:
Actually, your dates are not stored with any format: they are
_displayed_ with a format that you specify. Dates are stored as Numbers
with subtype Double. The whole number portion contains the number of
days since the seed date, and the decimal portion represents the time of
day: .0 = midnight, .5 = noon

So your point would have been more easily understood if you had said
that you stored both date and time in the field, rather than date-only
or time-only .

 

[Matthew Dyer]

 >=#11/1/2010# AND <#11/2/2010#

Or slower but it will work. Use a calculated field to return just the date
portion.
Field: DateValue(TheField)
Criteria: = #11/1/2010#

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



- Show quoted text -

Thanks John. I never thought to use the AND to set a less than value.
This makes lots of sense and my new query is running now. I'll let you
know how it goes. I also like the re-formatting of the field into
datevalue. i'm a beginner to access and database work in genereal.
Thanks for the help.

 

[Matthew Dyer]

*Udate* Query ran perfectly with the And function added.

Bob, sorry about the confusion. I am new to the database thing. I've
got a very good handle on Excel and should have made the connection
that the data is stored as a number (double) and then displayed with
the type of formatting requested.