Query criteria and dates

  • Thread starter Thread starter Gavin
  • Start date Start date
G

Gavin

Hi i have a database that stores telephone enquiries
received by front of house staff. One of the fields on
the entry form uses the Now() function as default to
insert the current date/time. I have made a form based on
a query and am trying to filter the records to show only
those records created on one particular day.
To do this so far i have, created an unbound invisable
field set with Now() as it's default, this gets the
current date. I then in the criteria field for my query
set the criteria to that unbound field ie. forms!myform!
myfield. When i try and run this query nothing appears
even though i know records exist... am i doing something
drastically wrong, i'm sure i've done this before and it
has worked. any suggestions on what i may be doing wrong,
or a better way to filter records of the current day would
be great.

Hope this is clear. Gavin
 
Hi Gavin,

The problem that you are encountering is most likely a
result of the fact that the date field is storing date
and time, so your filter is looking for any records that
match the current date and time exactly to the fraction
of a second(which would be none).

Try comparing only the date portion of the entry
date/time to the current time. You probably don't even
need the control on your form returning the current
date/time, but if you did it would be better to change
it's value to Date(), which returns the current date
without the time part.

Instead, in your query try setting the criteria that the
integer part of the date/time value = the current date.

such as:

WHERE Int([InquiryDateTime])=Date()

If using the query builder, just create a calculated
field such as:

InquiryDate: Int([InquiryDateTime])

and then enter Date() on the criteria line. Of course in
either case you will need to substitute your table's
date/time field for [InquiryDateTime].

The integer function returns the date portion of a
date/time value because date/time values are stored as a
decimal, with the integer part representing the date and
the decimal part representing time as a fraction of a day
(.5 = 12hours, etc).

HTH

-Ted Allen
 
Hi i have a database that stores telephone enquiries
received by front of house staff. One of the fields on
the entry form uses the Now() function as default to
insert the current date/time. I have made a form based on
a query and am trying to filter the records to show only
those records created on one particular day.
To do this so far i have, created an unbound invisable
field set with Now() as it's default, this gets the
current date. I then in the criteria field for my query
set the criteria to that unbound field ie. forms!myform!
myfield. When i try and run this query nothing appears
even though i know records exist... am i doing something
drastically wrong, i'm sure i've done this before and it
has worked. any suggestions on what i may be doing wrong,
or a better way to filter records of the current day would
be great.

Hope this is clear. Gavin
Click to expand...

If you are using a criteria of
= Now()
you will never get any records.
As you stated above, Now() includes the date and time, so =Now() will
be the current date and the current time.
No previously entered records match the criteria as their time value
is before the current time.

Date() returns the current date with a time value of Midnight.

Change your criteria to:
Between Date() and Now()

Only records with the current date, up to the time the query is run,
will be returned.

This, however, limits you to returning records only on that actual
date.

You can return records for any one date by using a parameter.
Add another column to the query.
Exp:Format([DateField],"m/d/yyyy")

As criteria for this column, write:
[Enter the Date Wanted]

The user will enter
3/7/2004
and the query will return all records for March 7 2004, regardless of
the time value.
Note: Regardless of your local date format usage, all dates must be
entered in US time format, month/day/year.
 
Back
Top