Networking Days in Access Query

[Otisp1218]

hello,

I have a query that I need to determine the number of business days between two dates? I am not sure how to do this. I believe I need to build a module with this function but am very new at this...is there anyone that can help?

My Query:

Workdays: ([AE_Resp_Rcvd]-[ReviewDate])

I am unsure how to build the function that will give the business days between my two fields in the query....

Thank you so much for any help. I did a search on line for code and did find some but I am unsure how to make it work - it is not.

L

[pbaldy]

It is typically done with a function. It would be easier to help if you posted the code you found and what goes wrong.

[Otisp1218]

I created a new module and copied the code I found in (Below): I named the Module NetworkdaysLp - now I am not sure if I did the correct thing. I pulled up my query and put the below.

Query - Workdays: NetworkdaysLp([AE_Resp_Rcvd]-[ReviewDate])

The error message I get in the query says "undefined function NetworkdaysLp in expression" and I am not sure what that means?

What is in Module

The top says (General) (Declarations)

Public Function BusinessDays(dteStartDate As Date, dteEndDate As Date) As Long
Dim lngYear As Long
Dim lngEYear As Long
Dim dteStart As Date, dteEnd As Date
Dim dteCurr As Date
Dim lngDay As Long
Dim lngDiff As Long
Dim lngACount As Long
Dim dteLoop As Variant
Dim blnHol As Boolean
Dim dteHoliday() As Date
Dim lngCount As Long, lngTotal As Long
Dim lngThanks As Long

dteStart = dteStartDate
dteEnd = dteEndDate

lngYear = DatePart("yyyy", dteStart)
lngEYear = DatePart("yyyy", dteEnd)

If lngYear <> lngEYear Then
lngDiff = (((lngEYear - lngYear) + 1) * 7) - 1
ReDim dteHoliday(lngDiff)
Else
ReDim dteHoliday(6)
End If

lngACount = -1

For lngCount = lngYear To lngEYear
lngACount = lngACount + 1
'July Fourth
dteHoliday(lngACount) = DateSerial(lngCount, 7, 4)

lngACount = lngACount + 1
'Christmas
dteHoliday(lngACount) = DateSerial(lngCount, 12, 25)

lngACount = lngACount + 1
'New Years
dteHoliday(lngACount) = DateSerial(lngCount, 1, 1)

lngACount = lngACount + 1
'Thanksgiving - 4th Thursday of November
lngDay = 1
lngThanks = 0
Do
If Weekday(DateSerial(lngCount, 11, lngDay)) = 5 Then
lngThanks = lngThanks + 1
End If
lngDay = lngDay + 1
Loop Until lngThanks = 4

dteHoliday(lngACount) = DateSerial(lngCount, 11, lngDay)

lngACount = lngACount + 1
'Memorial Day - Last Monday of May
lngDay = 31
Do
If Weekday(DateSerial(lngCount, 5, lngDay)) = 2 Then
dteHoliday(lngACount) = DateSerial(lngCount, 5, lngDay)
Else
lngDay = lngDay - 1
End If
Loop Until dteHoliday(lngACount) >= DateSerial(lngCount, 5, 1)

lngACount = lngACount + 1
'Labor Day - First Monday of Septemeber
lngDay = 1
Do
If Weekday(DateSerial(lngCount, 9, lngDay)) = 2 Then
dteHoliday(lngACount) = DateSerial(lngCount, 9, lngDay)
Else
lngDay = lngDay + 1
End If
Loop Until dteHoliday(lngACount) >= DateSerial(lngCount, 9, 1)
'MsgBox dteHoliday(5)

lngACount = lngACount + 1
'Easter
lngDay = (((255 - 11 * (lngCount Mod 19)) - 21) Mod 30) + 21

dteHoliday(lngACount) = DateSerial(lngCount, 3, 1) + lngDay + _
(lngDay > 48) + 6 - ((lngCount + lngCount \ 4 + _
lngDay + (lngDay > 48) + 1) Mod 7)
Next


For lngCount = 1 To DateDiff("d", dteStart, dteEnd)
dteCurr = (dteStart + lngCount)
If (Weekday(dteCurr) <> 1) And (Weekday(dteCurr) <> 7) Then
blnHol = False
For dteLoop = 0 To UBound(dteHoliday)
'MsgBox dteHoliday(dteLoop) & " " & dteLoop
If (dteHoliday(dteLoop) = dteCurr) Then
blnHol = True
End If
Next dteLoop
If blnHol = False Then
lngTotal = lngTotal + 1
'MsgBox dteCurr
End If
End If
Next lngCount
BusinessDays = lngTotal

End Function

thank you for help, sorry, I just don't understand...

L

[pbaldy]

Well for starters, you call the function, not the module. Also, you pass the two dates, not a formula. Try

Workdays: BusinessDays([AE_Resp_Rcvd], [ReviewDate])

Note that as written, the function will error if either field doesn't contain a date.

[Otisp1218]

Hello,

Thank you for this. Like I said, I apologize but I am new at creating Modules with functions...I did as you had below but received an error in the code and I am not sure what it means - could you help me with this? The code errors on the line below which is way down in the code.

Also, are you saying if there is no date in the field the code will error? Is there a way to fix this as I always won't have a date in the AE_Resp_Rcvd unless we have received a response from our customer?

Again, thank you for all your help.

Line it errors on - see below - ReDim dteHoliday(lngDiff)

Public Function BusinessDays(dteStartDate As Date, dteEndDate As Date) As Long
Dim lngYear As Long
Dim lngEYear As Long
Dim dteStart As Date, dteEnd As Date
Dim dteCurr As Date
Dim lngDay As Long
Dim lngDiff As Long
Dim lngACount As Long
Dim dteLoop As Variant
Dim blnHol As Boolean
Dim dteHoliday() As Date
Dim lngCount As Long, lngTotal As Long
Dim lngThanks As Long

dteStart = dteStartDate
dteEnd = dteEndDate

lngYear = DatePart("yyyy", dteStart)
lngEYear = DatePart("yyyy", dteEnd)

If lngYear <> lngEYear Then
lngDiff = (((lngEYear - lngYear) + 1) * 7) - 1
ReDim dteHoliday(lngDiff)
Else
ReDim dteHoliday(6)
End If

lngACount = -1


Thank you.

Lisa

Well for starters, you call the function, not the module. Also, you pass the two dates, not a formula. Try

Workdays: BusinessDays([AE_Resp_Rcvd], [ReviewDate])

Note that as written, the function will error if either field doesn't contain a date.

[pbaldy]

I rarely use arrays. What exactly is the error message? What dates were passed?

In order to avoid errors with no dates, you'd have to change the first line to use Variant instead of Date:

Public Function BusinessDays(dteStartDate As Variant, dteEndDate As Variant) As Long

and then within the function, test the variables with IsDate() to see if they are dates, and react accordingly.

[Otisp1218]

I added in what you have below and this is what I get.

I complied the code and got this.

Compile Error:

Function call on left-hand side of assignment must return Variant or Object? It points to this in the code....

BusinessDays= lngTotal

Is there a business days function that is less confusing?

Thanks again for the help.


L

I rarely use arrays. What exactly is the error message? What dates were passed?

In order to avoid errors with no dates, you'd have to change the first line to use Variant instead of Date:

Public Function BusinessDays(dteStartDate As Variant, dteEndDate As Variant) As Long

and then within the function, test the variables with IsDate() to see if they are dates, and react accordingly.

Not sure what you mean here ---- I am brand new at this and don't know code that well.

[pbaldy]

I work in a 24/7/365 business so I've never needed it, but this looks simpler:

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

[Otisp1218]

Thank you, I think I fixed it with your help - thank you.

I went into my query and reversed the order of my fields -

Workdays: Businessdays([Reveiw_Date)], (AE_Resp_Rcvd])

It ran and it looks good.

Thanks for all your help.

L

[pbaldy]

No problem, and welcome to the site by the way!

[Otisp1218]

thank you - you were a lot of help