Query a field with date/time date

  • Thread starter Thread starter David Lansberry
  • Start date Start date
D

David Lansberry

Hi:

I am trying to query a table's field that contains date
and time data. The data type is <Date/Time>.

Anyways, I would like to create a query that looks at only
the date part of this field and return results based on
this. Ex: "Count all invoices on May 10, 2004" while
ignoring the time the invoice data was entered.

Have tried: 1) 'DatePart' fcn. and, 2) formatting the
table's Date/Time data type in the Query Design view.

No success with either. Can anyone help? TIA. David
 
SELECT *
FROM YourTable
WHERE DateValue([YourDTField]) = #05/10/2004#

If you have many records in your Table and especially, if
YourDTField is indexed, this may be more efficient.

WHERE [YourDTField] >= #05/10/2004#
AND [YourDTField] < #05/11/2004#

HTH
Van T. Dinh
MVP (Access)
 
Try criteria of

Field: YourField
Criteria: >= #5/10/04# and <#5/11/04#

Or try using a calculated field, since this is calculated it will probably be
slower since the SQL cannot use any index created for the field.

Field: DateOnly: DateValue([TableName].[FieldName])
Criteria: #5/10/04#
 
Hi:

I am trying to query a table's field that contains date
and time data. The data type is <Date/Time>.

Anyways, I would like to create a query that looks at only
the date part of this field and return results based on
this. Ex: "Count all invoices on May 10, 2004" while
ignoring the time the invoice data was entered.
Click to expand...

A Date/Time is actually stored as a double float number, a count of
days and fractions of a day (times) since an arbitrary start date
(#12/30/1899 00:00:00# to be exact). There are two ways to do this
kind of search:

1. Use the DateValue() function to truncate the date to the date
portion only. This works but doesn't let Access use any index on the
field, so if the table is large can impact performance.

2. Use a criterion such as
= Format([Enter date:], "mm\/dd\/yyyy") AND < Format(DateAdd("d", 1, [Enter date:]))
Click to expand...

to search the range of dates from midnight at the start of the day to
the instant before midnight at the start of the next.
 
Back
Top