Query by two fields, Date & Time

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

Guest

I have a Date field and Time field. I have stored a Begin and End Date and
Time into a table so that I may use the values in a query. The query gives
me the Begin Date/Time and goes to the correct End Date, but doesn't stop at
the right End Time. I hope this is clear:

Beg Date Beg Time
1/1/06 10:00 AM
1/3/06 09:00 AM

But instead of stopping at 1/3/06 at 9:00 AM, it keeps going until the end
of 1/3/06.
 
Here is the SQL:

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]"))) OR
(((TIRTLhourly.DATE)=DLookUp("[EndDate]","[TIRTLBegEnd]")));

The criteria is for the Date Field, containing fields from a table. I want
to incorporate Begin Time and End Time from the table [TIRTLBegEnd] into the
query as well, to get an exact number of records. Thanks!
 
How many records are in the TIRTLBegEnd table? If it's more than one, there's
no way this is going to work unless your DLookUp’s have criteria.

Try the following:

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] BETWEEN DLookUp("[BegDate]", "TIRTLBegEnd")
AND DLookUp("[EndDate]", "TIRTLBegEnd") ;

Two problems that I see are the use of TYPE and DATE. Both are reserved
words in Access and can cause strange things to happen. Check out the
following for words to avoid as object and field names.
http://support.microsoft.com/kb/209187/EN-US/

Speaking of TYPE, I don’t see why you are using the Int function. 3 AS TYPE
should work.

Also judging by your field names, the database needs to be normalized.
 
Back
Top