Re: Search Date and Time

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.

"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:e3P8lVq8IHA.1180@xxxxxxxxxxxxxxxxxxxxxxx
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
http://I.Am/DougSteele
(no private e-mails, please)


"John Spencer" <spencer@xxxxxxxxx> wrote in message
news:%23zZLcej8IHA.3648@xxxxxxxxxxxxxxxxxxxxxxx
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] wrote:
OK, you've got me there :-)
Douglas J. Steele wrote:
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>


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:OHe8fwc8IHA.3736@xxxxxxxxxxxxxxxxxxxxxxx
It may be easier but it one needs to use an index to improve
performance, this approach negates any index use.

Douglas J. Steele wrote:
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#


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:uJmjaeW8IHA.2348@xxxxxxxxxxxxxxxxxxxxxxx
Bob Barrows [MVP] wrote:
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#)



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap
so I don't check it very often. If you must reply off-line, then
remove the "NO SPAM"
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"





.