Can't query datetime field formatted as m/d/yyyy


J

JohnOfOrtec

I need help with querying a field (called AddedDate) I want to see all
records prior to a given date. The field's data type is Datetime and the
format is m/d/yyyy, . The default value is set to Now(). It's intended to
be the date the record was added.

In short, can anyone tell me how to query this field to get all records
prior to a given date? I'm not having success querying it. I can query
other datetime fields in the table, but they are formatted as shortdate.
(FYI, I use the GUI, not the SQL view). Here are more details.

When I exported the AddedDate field to a text file, I see, for example,
11/3/2006 0:00:00. I put that in the query criteria, but Access
automatically changed it to #11/3/2006#, and I got no results. In my query,
I put in "11*" as my criteria and got every date that starts with 11. When I
use "between" criterial in my query, it will return the respective dates.
But when I try to query a specific date, or use <=#12/31/2008#, it returns
nothing.

I suspect this has to do with the m/d/yyyy format of the datetime.

I hope this is enough info. I greatly appreciate any help given.
John
 
Ad

Advertisements

K

KARL DEWEY

If the field is a DateTime datatype then format does not mean anything except
as a display function. Access stores the date as a decimal number with the
decimal being the fraction of a day. The date number ending in .25 is a
quarter of a day an will display as 6:00:00 AM.

Your using Now() put the date and time in the field.

Use a calculated field to apply your criteria like this --
Date_Only: DateValue([AddedDate])
or
Date_Only: CVDate(Format([AddedDate], "m/d/yyyy"))

The results of the Format function is a text string so above is converted to
a date.

Using #12/31/2008# will get you all records with that date, even those with
time.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top