Filter by date on a field that uses now()

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my table I have a field that calculates when any given record was created.
I did this by setting the defalut value to "now()". I changed the format so
it only reads the short date: 02/21/2007 and not the time.

I want to run a query that will only show the records created on a specific
day. I tried setting the criteria to [Enter Date Created]. I even tried
setting it to a specific date #2/21/2007#. But for some reason, when I run
the query, it does not return any results.

Any suggestions?
 
In my table I have a field that calculates when any given record was created.
I did this by setting the defalut value to "now()". I changed the format so
it only reads the short date: 02/21/2007 and not the time.

I want to run a query that will only show the records created on a specific
day. I tried setting the criteria to [Enter Date Created]. I even tried
setting it to a specific date #2/21/2007#. But for some reason, when I run
the query, it does not return any results.

Any suggestions?
Click to expand...

A date of #2/21/2007# is not the same as #2/21/2007 08:00:00 AM#

If you must store the time in the field, then use
Where Int([DateCreated]) = [What day?]
 
I'd rather not have the time in the now() field, but I don't know how to get
rid of it besides changing the format to short date.

fredg said:
In my table I have a field that calculates when any given record was created.
I did this by setting the defalut value to "now()". I changed the format so
it only reads the short date: 02/21/2007 and not the time.

I want to run a query that will only show the records created on a specific
day. I tried setting the criteria to [Enter Date Created]. I even tried
setting it to a specific date #2/21/2007#. But for some reason, when I run
the query, it does not return any results.

Any suggestions?
Click to expand...

A date of #2/21/2007# is not the same as #2/21/2007 08:00:00 AM#

If you must store the time in the field, then use
Where Int([DateCreated]) = [What day?]
Click to expand...
 
In my table I have a field that calculates when any given record was created.
I did this by setting the defalut value to "now()". I changed the format so
it only reads the short date: 02/21/2007 and not the time.

I want to run a query that will only show the records created on a specific
day. I tried setting the criteria to [Enter Date Created]. I even tried
setting it to a specific date #2/21/2007#. But for some reason, when I run
the query, it does not return any results.

Any suggestions?
Click to expand...

It's not returning results because Now() - regardless of how you
format it - doesn't contain today's date. It contains the current date
and time, accurate to a few microseconds. You won't easily get an
exact match!

Change the criterion to
= CDate([Enter Date Created:]) AND < DateAdd("d", 1, CDate([Enter Date Created:]))
Click to expand...

to a) convert user entered dates to Date/Time values using their
computer's regional date settings and b) find all records between
midnight and 11:59:59.9999999pm.

John W. Vinson [MVP]
 
Instead of using Now() to update the field, use Date() which will only
load the date part.

To adjust your prior data, run an update query that updates
DateCreated with the expression Fred gave you
Int([DateCreated]). And for clarity purposes only, change the date
format to shortdate on forms where it is used (and in the table
itself).

For clarity, do those three things in that sequence.

Ron
 
I'd rather not have the time in the now() field, but I don't know how to get
rid of it besides changing the format to short date.
Click to expand...

Just to reiterate - setting the format does NOT get rid of the time;
it just conceals it from view. The value in the table will still have
the time, and queries will need to deal with that.

Ron's suggestions should resolve the problem for you.

John W. Vinson [MVP]
 
Back
Top