Query using dates

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

Guest

Hi, I have a table with a field storing the time and date. I am trying to run
a query using the date only and I don’t get anything unless I enter the date
and time as it appear in the table. How can I specify in the query to look
only at the date such as 6/11/2006 and disregard the time?

Thank you,
Silvio
 
Silvio said:
Hi, I have a table with a field storing the time and date. I am
trying to run a query using the date only and I don't get anything
unless I enter the date and time as it appear in the table. How can I
specify in the query to look only at the date such as 6/11/2006 and
disregard the time?

Thank you,
Silvio
Click to expand...

Easy but inefficient...

WHERE DateValue([YourDateField]) = #6/11/2006#


Bit more typing, but more efficient...

WHERE [YourDateField] >= #6/11/2006#
AND [YourDateField] < #6/12/2006#

The difference is if you have an index on [YourDateField] the second method can
utilize it, the first method cannot and will have to scan all rows of the table.
 
Rick, how can I extract the date only from [YourDateField] using an
expression such as?:

Date: Date([YourDateField])


Rick Brandt said:
Silvio said:
Hi, I have a table with a field storing the time and date. I am
trying to run a query using the date only and I don't get anything
unless I enter the date and time as it appear in the table. How can I
specify in the query to look only at the date such as 6/11/2006 and
disregard the time?

Thank you,
Silvio
Click to expand...

Easy but inefficient...

WHERE DateValue([YourDateField]) = #6/11/2006#


Bit more typing, but more efficient...

WHERE [YourDateField] >= #6/11/2006#
AND [YourDateField] < #6/12/2006#

The difference is if you have an index on [YourDateField] the second method can
utilize it, the first method cannot and will have to scan all rows of the table.
Click to expand...
 
Silvio said:
Rick, how can I extract the date only from [YourDateField] using an
expression such as?:

Date: Date([YourDateField])
Click to expand...

You add a calculated field to your query....

SomeAlias: Date([YourDateField])

....and then under that in the criteria row you put the date you want.
 
Pardon me.

Rick, I think you meant to say

TheDate: DateValue([YourDateField])


Rick Brandt said:
Silvio said:
Rick, how can I extract the date only from [YourDateField] using an
expression such as?:

Date: Date([YourDateField])
Click to expand...

You add a calculated field to your query....

SomeAlias: Date([YourDateField])

...and then under that in the criteria row you put the date you want.
Click to expand...
 
Back
Top