Hi all.
I'm working on an Access 2003 query that will display how many working days it took to complete an order.
There are 2 dates: PromiseDate (or BegDate) and CompletionDate (or EndDate)
I also have a table called Holidays, which holds holiday dates (i.e. 12/25/2008, 01/01/2008) so they do not get included in the calculation and therefore not count against the length of time it took to complete an order...so it simply calculates the working days. I've been using the following function:
**************************
**********
**********
**********
**********
**********
**********
**********
**********
**********
*******
Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
DateCnt = BegDate
EndDays = 0
Do While DateCnt < EndDate
If Not IsNull(DLookup("HoliDate",
"Holidays", "[HoliDate]=#" & DateCnt & "#")) Then
EndDays = EndDays - 1
End If
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = EndDays
End Function
**************************
**********
**********
**********
**********
**********
**********
**********
**********
**********
**********
*
But as you can see, the loop condition is while the PromiseDate is before the CompletionDate, there may be times when we complete an order before the promise date.
In other words, let's say we promised to complete an order on 10/15/2008, but we actually complete the order on 10/14/2008. Then the calculated work days it took to complete it was -1.
Currently the query will display 0.
There are two parts where I'm having difficulty:
1. The loop condition (what if the promise date is larger than the completion date, as in the example above)
2. DateCnt = DateAdd("d", 1, DateCnt) --> Using the above example, it should subtract a day instead of add one, how do I do that?
Thank you in advance!
Start Free Trial
