On Time Delivery Query

[John Torres]

I’m trying to create a query or Report for “On Time Delivery”. I have query fields of PO, Date Received, Due Date and Date Shipped. I’d like to calculate EARLY (shipped 3 days early from the due date), On Time (Shipped on Due Date or 2 days early) and LATE (Shipped past Due Date) and on a business days only (excluding Saturday and Sunday). I would appreciate any ideas.
Thanks,
John

Access 2000
Vista

[Delerna]

Here is the query I came up with

SELECT Table1.PO, 
    Table1.Date, 
    Table1.Received, 
    Table1.DueDate, 
    Table1.ShippedDate, 
    Weekday([DueDate]) AS DueDay, 
    DateDiff("d",[DueDate],[ShippedDate]) AS NumDays, 
    IIf([DueDay]+[NumDays]<0 Or [DueDay]+[NumDays]>7,2,0)*IIf([NumDays]<0,-1,1) AS Weekends, 
    [numDays]-[weekends] AS WorkDays, 
    IIf([Workdays]<=-3,"Early",IIf([workdays]>0,"Late","On Time")) AS Timeliness
FROM Table1;

Basically I calculated
weekday is the day number of the delivery date
NumDays is the number of actual days it took to deliver
but you don't want to count the weekend so
if early then DueDay+Numdays will be less than 0 if there is at least 1 weekend
if late then DueDay+Numdays will be greater than 7 if there is at least 1 weekend
If either of the above are true i return 2 otherwise 0 and then adjust it to be either negative or positive depending on whether the delivery is early or late as weekends
Now all i needed to do is subtract weekends from NumDays to get the number of WorkDays used to make the delivery

I then used the value of WorkDays to return Ealy,On Time or Late according to your specs

Hope it helps

[John Torres]

Thanks for help but I got an answer from another forum with this code below for query and it works pretty good. But I want to modify the "On Time=Shipped on DueDate or 2 days early and 2 days later from due date".

Currently with this code is EARLY (shipped 3 days early from the due date), On Time (Shipped on Due Date or 2 days early) and LATE (Shipped past Due Date)

IIf([ShipDate]<=DateAdd("d" ,-3,[DueDate]),"Early",IIf ([ShipDate]>=DateAdd("d" ,-2,[DueDate]) And [ShipDate]<=[DueDate],"On Time","Late"))

Any help would be much appreciated.
Thanks,
John

Here is the query I came up with

SELECT Table1.PO, 
    Table1.Date, 
    Table1.Received, 
    Table1.DueDate, 
    Table1.ShippedDate, 
    Weekday([DueDate]) AS DueDay, 
    DateDiff("d",[DueDate],[ShippedDate]) AS NumDays, 
    IIf([DueDay]+[NumDays]<0 Or [DueDay]+[NumDays]>7,2,0)*IIf([NumDays]<0,-1,1) AS Weekends, 
    [numDays]-[weekends] AS WorkDays, 
    IIf([Workdays]<=-3,"Early",IIf([workdays]>0,"Late","On Time")) AS Timeliness
FROM Table1;

Basically I calculated
weekday is the day number of the delivery date
NumDays is the number of actual days it took to deliver
but you don't want to count the weekend so
if early then DueDay+Numdays will be less than 0 if there is at least 1 weekend
if late then DueDay+Numdays will be greater than 7 if there is at least 1 weekend
If either of the above are true i return 2 otherwise 0 and then adjust it to be either negative or positive depending on whether the delivery is early or late as weekends
Now all i needed to do is subtract weekends from NumDays to get the number of WorkDays used to make the delivery

I then used the value of WorkDays to return Ealy,On Time or Late according to your specs

Hope it helps