Calculating Time in Transit

[mssbass]

My data is as follows:

ShipDate: 2005-04-01 00:00:00.000
OrigZipCode 054
DestZipCode 054
ExpectedDeliverDate 2005-04-04 23:59:59.000
ActualDeliverDate 2005-04-05 12:15:00.000

I need some major help. I need to figure out what type of product is being shipped based on ship date and expected delivery date. I need to write a query that shows me how many business days each package took and if the package was delivered on a Saturday, I need to note that this was a Saturday delivery. I've got several thousand records. Is there a way to do this is a query statement rather than vba? I know nothing about vba.

Thank you

 

[lespaul]

This isn't exactly right, but should point you in the right direction (I use a different SQL flavor, so I don't always get the Access Jet SQL syntax right )

SELECT DateDiff(ShipDate, ActualDeliverDate, "d"), iif(DayOfWeek(ActualDeliverDate) = 6, "Yes", "No") As SaturdayDeliver FROM TableName

As far as getting the type of product that was being shipped, I don't see anything in this table that say anything about any product.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lynchg]

Select ShipDate, ExpectedDeliverDate, ActualDeliverDate, Weekday(ActualDeliverDate), DateDiff("d", ShipDate, ActualDeliverDate) as DeliveryDays;

The weekday function returns a 7 for Saturday dates. This will give you the basic data you were asking about. You could also add a where clause to restrict your output to just Saturdays (Where Weekday(ActualDeliverDate) = 7).

HTH

 

[mssbass]

I am having issues with the Weekday and DateDiff function. Right now, my date fields are formatted as text. I can ignore actual delivery date at this time. All that counts in order to figure out expected time in transit is ShipDate and ExpectedDeliverDate

You say this will return a 7 for Saturday dates? What if it's Monday - Friday? What about Sunday?

RESULTS NEEDED

If the package was shipped on a Friday and expected in 2 business days (Tuesday), I want the results to say 2.

However, if the package was shipped on Thursday or Friday and expected on Saturday, I want the results to just be null.

If the package was shipped on Monday and expected to be delivered the following Monday, I want the results to show '5', skipping over Saturday and Sunday = 5 business days.

How do I set up the query to read just the date only (no time involved)? Especially since my date fields are text??

 

[lynchg]

A few things.

You can't perform date functions like weekday() and datediff() on text fields.

You say your date fields are just text, but example of data you gave in your first post was datetime data. Can you change your date fields to be just a date data type? If you don't care about the time of day then don't use data type that includes more info than you need.

The weekday function returns a number corresponding to to that day of the week:

sunday = 1
monday = 2, etc.

Your last reply gives a lot more detail than your first, we could never have anticipated all of your needs in our initial replies.

Your criteria are more complicated than these 2 functions allow for. If I had these requirements I would need to develop some logic in code, it is not possible to throw all of the logic required to meet your conditions in just one query.

 

[BoxHead]

The following function could be pasted into a new module and saved. After you convert your string dates to actual dates, you can call it in a query using

Expr1: bizDays(Shipdate,ExpectedDeliverDate)

If you need holidays calculated in then don't use this and post back.

Function bizDays(D1 As Date, D2 As Date) As Integer
Dim X As Double, Y As Integer
Y = IIf(Weekday(D2) = 7, 1, 0)
For X = 1 To DateDiff("d", D1, D2)
If Weekday(DateAdd("d", X, D1)) > 1 And Weekday(DateAdd("d", X, D1)) < 7 Then
Y = Y + 1
End If
Next X
bizDays = Y
End Function

This function counts Saturday as a business day if the delivery is expected on Saturday. If you need a 'flag' for Saturday deliveries then like the other posts suggest, put a field in your query,
Expr2: Iif(WeekDay(ExpectedDeliverDate) = 7, "Saturday","")

Look in Help for the CDate(), Left(), and Mid() functions for converting your string to a date.


HTH

John




When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.