This is what I have working at the moment, I just need to be able to show
records that march from the date before and the date after as well...
SELECT Receiving.[Unit#], Receiving.[Trailer#], Receiving.PlanStart,
Receiving.Shift, Receiving.[Outslip#], Receiving.DateofUnload, [DC
Ship].[Unit #], [DC Ship].[Trailer#], [DC Ship].PlanStart, [DC Ship].Shift,
[DC Ship].LoadGroup, [DC Ship].ShipDate
FROM [DC Ship] INNER JOIN Receiving ON ([DC Ship].[Trailer#] =
Receiving.[Trailer#]) AND ([DC Ship].ShipDate = Receiving.DateofUnload)
WHERE (((Receiving.DateofUnload)=[DC Ship].[ShipDate]) AND
((Receiving.[Trailer#])=[DC Ship].[Trailer#]))
GROUP BY Receiving.[Unit#], Receiving.[Trailer#], Receiving.PlanStart,
Receiving.Shift, Receiving.[Outslip#], Receiving.DateofUnload, [DC
Ship].[Unit #], [DC Ship].[Trailer#], [DC Ship].PlanStart, [DC Ship].Shift,
[DC Ship].LoadGroup, [DC Ship].ShipDate;
KARL DEWEY said:
UNTESTED ---
SELECT [DC Ship].[Trailer#], [Receiving].[DateofUnload]
FROM [DC Ship].[Trailer#] LEFT JOIN [Receiving] ON [DC Ship].[Trailer#] =
[Receiving].[Trailer#]
WHERE (DateValue([DC Ship].[DateofUnload]) = CVDate([Enter Check Date]) OR
CVDate([Enter Check Date]) Is Null) AND
(DateValue([Receiving].[DateofUnload]) Between CVDate([Enter Check Date])-1
AND CVDate([Enter Check Date])+1);
Dragon said:
I have 2 tables 'DC Ship' and 'Receiving'. I need to compare the trailer
numbers (in both tables the field name is 'Trailer#') from a user specified
date in Receiving ('DateofUnload') -- defaulted to today's date -- to the
trailer #s in 'DC Ship' from the day before, same day and day after the user
specified date.
ie. If the user enters a date of 6/25/09, I want it to check receeiving for
that date against DC Ship dates of 6/24/09, 6/25/09, and 6/26/09.
Thanks,
Dragon