Business Days Code Issue

[chris3366]

Hello,

Up until today this coding has been working perfectly. I am using it with MS Access 2010 in 2003 mode because that is how old the database is. The dates are pulled from a form that uses text boxes to enter dates. The text boxes are formatted to Short Date and they work great. The problem is I am all of a sudden getting an overflow error. If I type the dates directly into the query that is using the module it will work just fine. But pulling it from the form I get the error. Here is the code. Any and all help will be appreciated.

PS: The error highlights on the last line beginning with "WorkDays" before End Function. If I hover over BegDate I see 05/26/207.

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' Note that this function does not account for holidays.

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays

End Function

 

[Andrzejek]

Is it possible you are trying to calculate the number of Work_Days that is more than the limit if an integer of 32,767?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

Based on what Andy is thinking try this mod

Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Long

' Note that this function does not account for holidays.

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As long
If isdate(begdate) and if isdate(enddate) then
  BegDate = DateValue(BegDate)
  EndDate = DateValue(EndDate)
  WholeWeeks = DateDiff("w", BegDate, EndDate)
  DateCnt = DateAdd("ww", WholeWeeks, BegDate)
  EndDays = 0
  Do While DateCnt < EndDate
  If Format(DateCnt, "ddd") <> "Sun" And _
    Format(DateCnt, "ddd") <> "Sat" Then
    EndDays = EndDays + 1
  End If
  DateCnt = DateAdd("d", 1, DateCnt)
  Loop
  Work_Days = WholeWeeks * 5 + EndDays
end if

End Function

 

[chris3366]

Andrzejek (Andy) - Thanks for the response. Date range being used is 05/22/2016 thru 5/28/2016. Keep in mind if I hard code the dates into the query it will work. We usually use a week date range and I have used this for a month or even 2 month range. Thanks again

MajP - I will give it a go and let you know. Thanks!

 

[Andrzejek]

Not to be picky, but you said: "If I hover over BegDate I see 05/26/207."
Year 207 was about 1800 years ago, which would make your Work_Days way over the Integer's limit.

Could you run this check and show results:

  ...[blue]
  Debug.Print "WholeWeeks: " & WholeWeeks
  Debug.Print "EndDays: " & EndDays
  Debug.Print "Last Calculation: " & WholeWeeks * 5 + EndDays
[/blue]
  Work_Days = WholeWeeks * 5 + EndDays
End If

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[chris3366]

Andy - You are absolutely correct. That is what it says when I go into the debugger. I understand your questioning that. But that is what it says. Weird. I will run the Debug.print and see what pops out. Thanks!

 

[strongm]

>If I hover over BegDate I see 05/26/207.

Well, yes, that would certainly do it!

So, let's see - what is the value of BegDate when you enter the function (not what you *think* is being passed). Put a break on

BegDate = DateValue(BegDate)

and then let's see the value there.