IT SOLUTIONS
Your full service technology partner! 
-Collapse +Expand
Access
Search Access Group:

Advanced
-Collapse +Expand Access To/From
To/FromCODEGuides
-Collapse +Expand Access Store
PRESTWOODSTORE

Prestwood eMagazine

December Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyMicrosoft Access BoardAccess VBA Programming Topic   Print This     

Count Number of dates w/holidays

Count Number of dates w/holidays in Access VBA Programming topic (part of our Microsoft Access group).

Quick Search: w/holidays   Count Number   Count Number of   dates w/holidays  
Mr. Pickles
 (Inactive)
In the Jar
Ok, so I took some code of the MS site and modified it to take Holidays into account while counting.

It works most of the time, but it is not 100% accurate. Can anyone see why?

Code as Follows:

   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
If IsNull(DLookup("HolidayDate", "tblHolidays", "[HolidayDate] = #" & DateCnt & "#")) Then
EndDays = EndDays + 1
End If
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
 Posted 19 years ago (Thread Starter)
Comment Quote
About Mr. Pickles -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #6499, 6 replies
Thread Started 6/5/2002 8:27:00 AM
View Counter=2140
Location=In the Jar  
Joined=19 years ago   MB Posts=8  
jdschram
MN
Your problem is probably here
If IsNull(DLookup("HolidayDate", "tblHolidays", "[HolidayDate] = #" & DateCnt & "#")) Then            
EndDays = EndDays + 1
End If
it is not going to increment enddays in any instance because your if statement is wrong...


it should be
If IsNull DLookup "HolidayDate", "tblHolidays", "[HolidayDate] = #" & DateCnt & "#")) = "False" Then            
EndDays = EndDays + 1
End If
I think it should be "False" but if it is not adding it change it to "True"

NOTE True and False are case sensitive
 Posted 19 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #6508 (Level 1.1)  Reply to 6499
Thread Started 6/5/2002 8:56:00 AM
View Counter=2
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
Dont't really know what your problem is then...I think it may be a loginc problem...what exactly are you trying to do? Get the total number of days, the total number of work days, etc...it may help me to help you a little more
 Posted 19 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #6515 (Level 1.2)  Reply to 6499
Reply Posted 6/5/2002 10:25:00 AM
Location=MN  
Joined=20 years ago   MB Posts=259  
jdschram
MN
anyway...ignore my previous post...
I whipped this up in VBA the only differences you will need is a way to replace 5/27/02 with any date...I think there was just something wrong with your logic...
Private Sub Command1_Click()
Dim BegDate As String
BegDate = "5/14/2002"
Dim EndDate As String
EndDate = "5/31/2002"
Dim wholeweeks As String
wholeweeks = DateDiff("w", BegDate, EndDate)
Dim totalDays As String
totalDays = DateDiff("d", BegDate, EndDate)
Dim enddays As Integer
enddays = 0
Dim flag As Integer
flag = 1
work_days = totalDays

Dim msgboxtext As String
While flag = 1
If CStr(BegDate) = CStr(EndDate) Then
flag = 0
Else
BegDate = DateAdd("d", 1, BegDate)
If InStr(FormatDateTime(BegDate, 1), "Saturday") Or InStr(FormatDateTime(BegDate, 1), "Sunday") Then
work_days = work_days - 1
ElseIf BegDate = "5/27/2002" Then
work_days = work_days - 1
Else
msgboxtext = msgboxtext & FormatDateTime(BegDate, 1) & Chr(13)
End If
End If
Wend
MsgBox "WORKDAYS LISTED:" & Chr(13) & Chr(13) & msgboxtext
Beep
MsgBox "WORKDAYS: " & work_days & Chr(13) & Chr(13) & "WHOLEWEEKS: " & _
wholeweeks & Chr(13) & Chr(13) & "TOTAL DAYS: " & totalDays



End Sub
 Posted 19 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #6516 (Level 1.3)  Reply to 6499
Reply Posted 6/5/2002 10:59:00 AM
Location=MN  
Joined=20 years ago   MB Posts=259  
Mr. Pickles
 (Inactive)
In the Jar
Thanks, I'll check it out.
 Posted 19 years ago (Thread Starter)
Comment Quote
About Mr. Pickles -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #6513 (Level 1.4)  Reply to 6499
Reply Posted 6/5/2002 9:23:00 AM
Location=In the Jar  
Joined=19 years ago   MB Posts=8  
Mr. Pickles
 (Inactive)
In the Jar
Nope, it gives me the same results.

btw, it was True not False.

For instance 05/14/2002 through 05/31/2002 results in 13 days. It should be 12.

However, 05/20/2002 through 05/28/2002 results in 5, which is correct.
 Posted 19 years ago (Thread Starter)
Comment Quote
About Mr. Pickles -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #6514 (Level 1.5)  Reply to 6499
Reply Posted 6/5/2002 9:31:00 AM
Location=In the Jar  
Joined=19 years ago   MB Posts=8  
Most Recent Post
Mr. Pickles
 (Inactive)
In the Jar
I'm trying to count the number of working days between two dates. I also need to take Holidays into consideration and not count them.
 Posted 19 years ago (Thread Starter)
Comment Quote
About Mr. Pickles -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #6517 (Level 1.6)  Reply to 6499
Reply Posted 6/5/2002 11:04:00 AM
Location=In the Jar  
Joined=19 years ago   MB Posts=8  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P195A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #6499 Counter
2140
Since 4/2/2008
Go ahead!   Use Us! Call: 916-726-5675  Or visit our new sales site: 
www.prestwood.com


©1995-2021 Prestwood IT Solutions.   [Security & Privacy]