comparing date field and time field in two tables

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

Guest

Using an access query I am comparing a date field and a time field in two
tables (tblPermit_ss & tblExemptionsInfo). My query displays only records
that do not match. I am using the following query and it works fine.
SELECT fldCompanyName, fldOneWorldNumber, fldBridgeNumber, fldDate, fldTime,
fldLane, fldTagNumber
FROM tblPermit_ss
WHERE (((tblPermit_ss.fldTime) Not In (Select fldTime From
tblExemptionsInfo))) OR (((tblPermit_ss.fldDate) Not In (Select fldDate From
tblExemptionsInfo)));
My problem is; I need to allow for a 15+/- minute window for the time.
HELP!
 
Well you actually would be better off in the dates and times were not in
separate fields. Then you could use a non-equi join.

PERHAPS the following MIGHT work.

SELECT fldCompanyName, fldOneWorldNumber, fldBridgeNumber, fldDate, fldTime,
fldLane, fldTagNumber
FROM tblPermit_ss as P LEFT JOIN tblExemptionsInfo as E
ON (P.fldDate + P.FldTime)
Between DateAdd("n",-15,E.FldDate + E.FldTime) and
DateAdd("n",+15,E.FldDate + E.FldTime)
WHERE E.FldDate Is Null

What I've attempted to do is to add the dates and times together,
add an interval of +/- 15 minutes to the second set of times,
Join on that range,
and finally use the where clause to say there is nothing (null) matching in the
second table.
 
Back
Top