INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Business Days Code Issue

Business Days Code Issue

(OP)
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

RE: Business Days Code Issue

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.

RE: Business Days Code Issue

Based on what Andy is thinking try this mod

CODE

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 

RE: Business Days Code Issue

(OP)
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!

RE: Business Days Code Issue

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. wavey

Could you run this check and show results:

CODE

...
  Debug.Print "WholeWeeks: " & WholeWeeks
  Debug.Print "EndDays: " & EndDays
  Debug.Print "Last Calculation: " & WholeWeeks * 5 + EndDays

  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.

RE: Business Days Code Issue

(OP)
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!

RE: Business Days Code Issue

>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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close