Date query using Date()

I've been using Date() to filter for various records (Completed After
Deadline Date, Before Deadline Date etc.). However, I cannot get it to filter
for records with "Deadline" = current date.

In the query criteria field for "Deadline" I put:

=Date()

but no records are displayed, even though I have ensured there is a
"Deadline" field with todays date stored in it.

Ideas?

Thanks

Dave

Is your Deadline field a Date/Time field or a Text field?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conrad...essjunkie.html



"David M C" wrote in message
...
I've been using Date() to filter for various records (Completed After
Deadline Date, Before Deadline Date etc.). However, I cannot get it to
filter
for records with "Deadline" = current date.

In the query criteria field for "Deadline" I put:

=Date()

but no records are displayed, even though I have ensured there is a
"Deadline" field with todays date stored in it.

Ideas?

Thanks

Dave

Try =Now()

"David M C" wrote:

I've been using Date() to filter for various records (Completed After
Deadline Date, Before Deadline Date etc.). However, I cannot get it to filter
for records with "Deadline" = current date.

In the query criteria field for "Deadline" I put:

=Date()

but no records are displayed, even though I have ensured there is a
"Deadline" field with todays date stored in it.

Ideas?

Thanks

Dave

If you enter the date directly in the query, are any records returned?

Field: Deadline
Criteria: #12/22/2005#

If that fails to return records then your Deadline field probably contains a
time component as well as the date. So try using a range

Field: Deadline
Criteria: =#12/22/2005# and #12/23/2005#

If that fails - are there other criteria in the query that would cause no
records to be returned for the date?


"David M C" wrote in message
...
I've been using Date() to filter for various records (Completed After
Deadline Date, Before Deadline Date etc.). However, I cannot get it to
filter
for records with "Deadline" = current date.

In the query criteria field for "Deadline" I put:

=Date()

but no records are displayed, even though I have ensured there is a
"Deadline" field with todays date stored in it.

Ideas?

Thanks

Dave

On Thu, 22 Dec 2005 08:36:03 -0800, David M C wrote:

I've been using Date() to filter for various records (Completed After
Deadline Date, Before Deadline Date etc.). However, I cannot get it to filter
for records with "Deadline" = current date.

In the query criteria field for "Deadline" I put:

=Date()

but no records are displayed, even though I have ensured there is a
"Deadline" field with todays date stored in it.

Ideas?

Thanks

Dave

My guess is that your date field includes a time value.
12/22/2005 is not equal to 12/22/2005 09:15 AM.

If so, your criteria should be
Where [Deadline] =Date() And Date()+1
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Deadline format is Date/Time. How would I change it to be Date only? When
entering new records, if you only input a Date, what does Access do with the
Time part?

Thanks

Dave

"fredg" wrote:

On Thu, 22 Dec 2005 08:36:03 -0800, David M C wrote:

I've been using Date() to filter for various records (Completed After
Deadline Date, Before Deadline Date etc.). However, I cannot get it to filter
for records with "Deadline" = current date.

In the query criteria field for "Deadline" I put:

=Date()

but no records are displayed, even though I have ensured there is a
"Deadline" field with todays date stored in it.

Ideas?

Thanks

Dave

My guess is that your date field includes a time value.
12/22/2005 is not equal to 12/22/2005 09:15 AM.

If so, your criteria should be
Where [Deadline] =Date() And Date()+1
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Access doesn't have a Date only (nor a Time only) data type, just the
Date/Time data type.

Under the covers, the date/time is stored as an 8 byte floating point
number, where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents the time as a
fraction of a day. If you don't supply a time, Access assumes midnight at
the start of the day.

How are you populating the Deadline field? If you're using the Now()
function, change it to Date().

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"David M C" wrote in message
...
Deadline format is Date/Time. How would I change it to be Date only? When
entering new records, if you only input a Date, what does Access do with
the
Time part?

Thanks

Dave

"fredg" wrote:

On Thu, 22 Dec 2005 08:36:03 -0800, David M C wrote:

I've been using Date() to filter for various records (Completed After
Deadline Date, Before Deadline Date etc.). However, I cannot get it to
filter
for records with "Deadline" = current date.

In the query criteria field for "Deadline" I put:

=Date()

but no records are displayed, even though I have ensured there is a
"Deadline" field with todays date stored in it.

Ideas?

Thanks

Dave

My guess is that your date field includes a time value.
12/22/2005 is not equal to 12/22/2005 09:15 AM.

If so, your criteria should be
Where [Deadline] =Date() And Date()+1
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Deadline is populated manually through a form. The user inputting the latest
job enters the date it must be finished by.

I think I'll have to use the suggestion in one of the replies above.

"Douglas J. Steele" wrote:

Access doesn't have a Date only (nor a Time only) data type, just the
Date/Time data type.

Under the covers, the date/time is stored as an 8 byte floating point
number, where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents the time as a
fraction of a day. If you don't supply a time, Access assumes midnight at
the start of the day.

How are you populating the Deadline field? If you're using the Now()
function, change it to Date().

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"David M C" wrote in message
...
Deadline format is Date/Time. How would I change it to be Date only? When
entering new records, if you only input a Date, what does Access do with
the
Time part?

Thanks

Dave

"fredg" wrote:

On Thu, 22 Dec 2005 08:36:03 -0800, David M C wrote:

I've been using Date() to filter for various records (Completed After
Deadline Date, Before Deadline Date etc.). However, I cannot get it to
filter
for records with "Deadline" = current date.

In the query criteria field for "Deadline" I put:

=Date()

but no records are displayed, even though I have ensured there is a
"Deadline" field with todays date stored in it.

Ideas?

Thanks

Dave

My guess is that your date field includes a time value.
12/22/2005 is not equal to 12/22/2005 09:15 AM.

If so, your criteria should be
Where [Deadline] =Date() And Date()+1
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail