Search Date and Time

[Floyd Forbes]

How do you query between dates and times in access? I am trying to query
between #7/23/2008 6:00:00 PM# and #7/24/2008 6:00:00 AM# . How do I get
this to work? Please Help!

Floyd

 

[John W. Vinson/MVP]

How do you query between dates and times in access? I am trying to query
between #7/23/2008 6:00:00 PM# and #7/24/2008 6:00:00 AM# . How do I get
this to work? Please Help!

Floyd

Ummm....

You answered your own question.

Use a criterion on the datefield of

between #7/23/2008 6:00:00 PM# and #7/24/2008 6:00:00 AM#

Or, equivalently,

= #7/23/2008 6:00:00 PM# and <= #7/24/2008 6:00:00 AM#

Or for more flexibility,

BETWEEN [Forms]![SomeFormName]![txtStartTime] AND
[Forms]![SomeFormName]![txtEndTime]

to use unbound form controls of those names to enter the criteria.

 

[Floyd Forbes]

How do I get this to work if date is in one column and time in another
column?
Can I still use the same expression?

Thank you

How do you query between dates and times in access? I am trying to query
between #7/23/2008 6:00:00 PM# and #7/24/2008 6:00:00 AM# . How do I get
this to work? Please Help!

Floyd

Ummm....

You answered your own question.

Use a criterion on the datefield of

between #7/23/2008 6:00:00 PM# and #7/24/2008 6:00:00 AM#

Or, equivalently,

= #7/23/2008 6:00:00 PM# and <= #7/24/2008 6:00:00 AM#

Or for more flexibility,

BETWEEN [Forms]![SomeFormName]![txtStartTime] AND
[Forms]![SomeFormName]![txtEndTime]

to use unbound form controls of those names to enter the criteria.

 

[Bob Barrows [MVP]]

How do you query between dates and times in access? I am trying to
query between #7/23/2008 6:00:00 PM# and #7/24/2008 6:00:00 AM# . How
do I get this to work? Please Help!

Errr ...

BETWEEN #7/23/2008 6:00:00 PM# and #7/24/2008 6:00:00 AM#

should work. If not, we need more details. Start by switching your query to
SQL View and copy/pasting the sql statement into your reply to this. Verify
that the field you are attempting to filter is indeed a Date/Time field that
contains dates and times.

 

[Bob Barrows [MVP]]

No. That is why we usually recommend storing both date and time in a single
Date/Time field.

If you have some reason not to be able to comfine them, you will need to use
compound criteria for both fields. This will be easier to demonstrate using
sql, so switch your query to SQL View using the View menu, or the toolbar
button, or the right-click context menu. Assuming DateField and TimeField
are both Date/Time fields, the WHERE clause should look something like this:

WHERE (DateField = #7/23/2008# AND timeField >= 6:00:00 PM#)
OR
(DateField = #7/24/2008# AND timeField <= 6:00:00 AM#)

How do I get this to work if date is in one column and time in another
column?
Can I still use the same expression?

Thank you

How do you query between dates and times in access? I am trying to
query between #7/23/2008 6:00:00 PM# and #7/24/2008 6:00:00 AM# .
How do I get this to work? Please Help!

Floyd

Ummm....

You answered your own question.

Use a criterion on the datefield of

between #7/23/2008 6:00:00 PM# and #7/24/2008 6:00:00 AM#

Or, equivalently,

= #7/23/2008 6:00:00 PM# and <= #7/24/2008 6:00:00 AM#

Or for more flexibility,

BETWEEN [Forms]![SomeFormName]![txtStartTime] AND
[Forms]![SomeFormName]![txtEndTime]

to use unbound form controls of those names to enter the criteria.

 

[John W. Vinson/MVP]

How do I get this to work if date is in one column and time in another
column?
Can I still use the same expression?

Not directly. As Bob says, it's best to have the date and time
combined. As an alternative to Bob's suggestion you can use a Query
with a calculated field:

DateAndTime: [datefield] + [timefield]

and use the BETWEEN criterion on that field. However, this will lose
the benefit of any indexes on the fields; Bob's suggestion preserves
the benefit.

 

[Bob Barrows [MVP]]

No. That is why we usually recommend storing both date and time in a
single Date/Time field.

If you have some reason not to be able to comfine them, you will need
to use compound criteria for both fields. This will be easier to
demonstrate using sql, so switch your query to SQL View using the
View menu, or the toolbar button, or the right-click context menu.
Assuming DateField and TimeField are both Date/Time fields, the WHERE
clause should look something like this:
WHERE (DateField = #7/23/2008# AND timeField >= 6:00:00 PM#)
OR
(DateField = #7/24/2008# AND timeField <= 6:00:00 AM#)

Typo ... it should be:

WHERE (DateField = #7/23/2008# AND timeField >= #6:00:00 PM#)
OR
(DateField = #7/24/2008# AND timeField <= #6:00:00 AM#)

 

[Douglas J. Steele]

While that will work in this particular example, it's far easier just to add
the date and time together:

WHERE (DateField + TimeField) BETWEEN #7/23/2008 6:00:00 PM# and #7/24/2008
6:00:00 AM#

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Bob Barrows [MVP]]

It may be easier but it one needs to use an index to improve performance,
this approach negates any index use.

 

[Douglas J. Steele]

I would think that if the developer is sophisticated enough to have created
indexes based on expected query paths, he/she would also be sophisticated
enough to realize that the date and time should be stored in a single field.
<g>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Bob Barrows [MVP]]

OK, you've got me there

I would think that if the developer is sophisticated enough to have
created indexes based on expected query paths, he/she would also be
sophisticated enough to realize that the date and time should be
stored in a single field. <g>

 

[John Spencer]

One other point (which I think was implied by Douglas Steele) is that for a
range over more than a 24 hour period Bob Barrow's solution would become

WHERE (DateField = #7/23/2008# AND timeField >= #6:00:00 PM#)
OR DateField Between #7/24/2008# and #7/25/2008#
OR (DateField = #7/26/2008# AND timeField <= #6:00:00 AM#)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob Barrows [MVP]]

.... and all those ORs are making me wonder if indexes would be used in
either case.
I've no time to test it now, but my gut says the ORs make it
nonsargable.

 

[Douglas J. Steele]

Thanks, John. I was too lazy to work out the specifics!

I got burned by that years and years ago when we implemented an application
in DB2 before they even had a Date data type in the database!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Floyd Forbes]

Thanks for all the help. But I'm having a hard time getting this to work.
Having a date field and time field is giving me problems.

 

[John Spencer]

As noted, the easiest (not the most efficient method) is to create a
calculated field in your query and then apply criteria against that

Assuming that your records always have both a date and time in the fields Or
both fields are always blank AND the fields are both defined as DateTime fields.

Field: CombinedDT: [TableName].[DateField] + [TableName].[TimeField]
Criteria: Between #7/23/2008 6:00:00 PM# and #7/24/2008 6:00:00 AM#

IF you might have a date field with no value in the timefield then you need

Field: CombinedDT: [TableName].[DateField] +
Nz([TableName].[TimeField],#00:00:00#)
Criteria: Between #7/23/2008 6:00:00 PM# and #7/24/2008 6:00:00 AM#

IF you fields are not DateTime fields but are text fields containing strings
that look like dates and times, post back for a more complex expression using
DateValue and TimeValue.

Field: CombinedDT: DateValue([TableName].[DateField]) +
TimeValue(Nz([TableName].[TimeField],#00:00:00#))
Criteria: Between #7/23/2008 6:00:00 PM# and #7/24/2008 6:00:00 AM#

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County