Date query using Date()

D

David M C

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
 
J

John Spencer

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?
 
F

fredg

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
Click to expand...

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
 
D

David M C

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 said:
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
Click to expand...

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
Click to expand...
 
D

Douglas J. Steele

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

(no e-mails, please!)



David M C said:
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 said:
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
Click to expand...

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
Click to expand...
Click to expand...
 
D

David M C

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 said:
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

(no e-mails, please!)



David M C said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Top