Date difference

[ridhirao22]

Hi,

Using MS Access 2003

I have to find the difference between 2 dates (orderdate and ship date) and find the percentage of orders which fall with 30,45,60 days.

I did is using datediff("d",[orderdate],[shipdate]) gives me the number of days


How can I find the count of orders with different period buckets(30,45,60 days) in single query?

TIA,
RR

 

[dhookom]

I would use a table that describes the ranges but I expect this might be overkill for your needs. Consider creating a simple function that accepts the two dates and returns the bucket value. What isn't clear to us is if the ShipDate might be null. Assuming it won't be try:

Public Function GetDateBucket(datOrder As Date, datShip As Date) As String
    Dim intDays As Integer
    intDays = DateDiff("d", datOrder, datShip)
    Select Case intDays
        Case 0 To 30
            GetDateBucket = "0 to 30 Days"
        Case 31 To 45
            GetDateBucket = "31 to 45 Days"
        Case 46 To 60
            GetDateBucket = "46 to 60 Days"
        Case Else
            GetDateBucket = "Greater than 60 Days"
    End Select
End Function

Copy the above code into a new module and save the module with the name "modDateFunctions".
You can then use the function everywhere in your application.


Duane
Hook'D on Access
MS Access MVP

 

[ridhirao22]

Yes Ship dates a null in few case

 

[dhookom]

ridhirao22,
I was hoping you might have suggested what results you would expect if the ship date is null.

Did you try the code?

Does it meet your needs?

Do you understand the suggestion?


Duane
Hook'D on Access
MS Access MVP