Date Range Comparison

D

Dragon

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
 
K

KARL DEWEY

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);
 
D

Dragon

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
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
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#])
WHERE (Receiving.DateofUnload) Between [DC Ship].ShipDate-1 AND [DC
Ship].ShipDate+1)
ORDER 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;



Dragon said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fiscal year 2
Serious Problem with a Date count fields & YES/No field 4
Numbering....??? 7
SQL SUM TOTAL Date Range Issue 3
How to reference all dates to a "Monday" 4
Date Range in Query using DateAdd 4
Convert Julian Date to mm/dd/yyyy 5
VLOOKUP with multiple lookup_value's 3

Top