How query NOW() date field?

  • Thread starter Thread starter SteveL
  • Start date Start date
S

SteveL

I have a table which has a date field in it which
defaults to =NOW(). But I need to write a query with
criteria looking for a short date. For example, if I
look for "1/12/04" I don't get a record returned that
contains "1/12/2004 4:36:40 PM" I suppose because of all
the extra characters in the table field.

Anyone know how to solve this?

--Steve
 
I have a table which has a date field in it which
defaults to =NOW(). But I need to write a query with
criteria looking for a short date. For example, if I
look for "1/12/04" I don't get a record returned that
contains "1/12/2004 4:36:40 PM" I suppose because of all
the extra characters in the table field.

Anyone know how to solve this?

--Steve
Click to expand...

use
= Date()
not Now().
 
Or, if you cannot replace Now with Date in your database, then add a
calculated field to your query and put your criterion on that calculated
field:

DateOnly: DateValue([FieldName])
 
I have a table which has a date field in it which
defaults to =NOW().
Click to expand...

In that case it contains the date and time accurate to microseconds.
But I need to write a query with
criteria looking for a short date. For example, if I
look for "1/12/04" I don't get a record returned that
contains "1/12/2004 4:36:40 PM" I suppose because of all
the extra characters in the table field.
Click to expand...

Well... sort of. It would actually contain a double floating point
number, 37998.6921296296 to be exact; that's how many days and
fractions of a day that moment is since midnight December 30, 1899.

Three ways around this:

- If you don't need the time portion for some other reason, default
the field to Date() instead of Now(). It will go in with just the date
portion, midnight (.00000000000) as the time. You can run an Update
query updating existing records to set this field to
Datevalue([fieldname]) to discard the existing times.

- Or, use Datevalue([fieldname]) as a calculated field in the query
and apply your criterion to this field. This will work but may be slow
if the table is large, since you'll need to call a function on every
row and won't get any benefit from an index on the field.

- Or, use a criterion of
= [Enter date:] AND < DateAdd("d", 1, [Enter date:])
Click to expand...

to search (using indexes!) all date/times within that one day range.
 
Back
Top