Query on 2 fields

[Kou Vang]

How do I query on a Date field and a Hour field to get 24 or 48 hour
intervals between 2 dates? I can get the correct criteria for the Date
field, but how do I write a correct criteria statement to get it right? Exp:

11/1/2005 2:00 PM
11/3/2005 2:00 PM

I have these 4 values stored in a table for reference.

Here is the SQL code I have for my query:

SELECT DLookUp("[Site]","[TIRTLBegEnd]") AS SITE, TIRTLhourly.DATE,
TIRTLhourly.TIME, TIRTLhourly.LANE, TIRTLhourly.TOTAL, TIRTLhourly.[1],
TIRTLhourly.[2], TIRTLhourly.[3], TIRTLhourly.[4], TIRTLhourly.[5],
TIRTLhourly.[6], TIRTLhourly.[7], TIRTLhourly.[8], TIRTLhourly.[9],
TIRTLhourly.[10], TIRTLhourly.[11], TIRTLhourly.[12], TIRTLhourly.[13],
TIRTLhourly.[14], TIRTLhourly.[15], Int("3") AS TYPE
FROM TIRTLhourly
WHERE (((TIRTLhourly.DATE)>=DLookUp("[BegDate]","[TIRTLBegEnd]") And
(TIRTLhourly.DATE)<=DLookUp("[EndDate]","[TIRTLBegEnd]")) AND
((TIRTLhourly.TIME)>=DLookUp("[BegTime]","[TIRTLBegEnd]"))) OR
(((TIRTLhourly.DATE)=DLookUp("[EndDate]","[TIRTLBegEnd]")));

The result is, that I get all the hours greater than the begin time, but
once it gets past midnight, it goes straight to the next day and starts again
at the begin time. So I miss all the hours of the 2nd day from 1 AM til the
Begin time.

 

[Tom Ellison]

Dear Kou Vang:

It would be easier for you if you combine the date and time values into a
single date/time value. You would then have two fields instead of 4. The
built in functions like DateAdd (which sounds like what you want) work on
values that have date and time combined. Same with comparison functions.

Just how you will have to do this will depend on how your date and time
values are stored now. Are they strings? What?

Tom Ellison

 

[Kou Vang]

I assume they are string, since the data type in the table is Date/Time? Is
that correct?

 

[Tom Ellison]

Dear Kou:

A Date/Time is stored as a floating point number. It can be formatted from
there as desired.

Tom Ellison

 

[Kou Vang]

If they are both floating point numbers, then how do I combine the two fields
into one value?

 

[Tom Ellison]

Dear Kou:

It may be that the way you have done this results in the date portion being
all integer portions, and the time portion is to the right of the decimal
place. If so, you could try adding them together. That's actually the way
it works. Otherwise, you may want to use the DateSerial function.

Tom Ellison