Date query using Date()

[Guest]

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

 

[Lynn Trapp]

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/conradsystems/accessjunkie.html

 

[Guest]

Try =Now()

 

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

 

[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

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

 

[Guest]

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

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

 

[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!)

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

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

 

[Guest]

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.

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!)

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

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