Counting Days in Access

Help..please!

I'm a rookie in terms of VB, but I'm desperately trying to make Access
count the number of days between two dates and then subtract the
weekends. Can anyone tell me what is wrong with the follwing code:

Option Compare Database

Public Function WorkingDays(StartDate As Date, EndDate As Date) As
Integer

On Error GoTo Err_WorkingDays

Dim intCountA As Integer

If StartDate Is Empty Then
intCountA = 0
Else

intCountA = 0
Do While StartDate <= EndDate
Select Case Weekday(StartDate)
Case Is = 1, 7
intCountA = intCountA
Case Is = 2, 3, 4, 5, 6
intCountA = intCountA + 1
End Select
StartDate = StartDate + 1
Loop

WorkingDays = intCountA

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

re: Counting Days in Access

I haven't tried to analyze your code, but it apparently doesn't take
holidays into account. A solution I've used includes generating an
array for dates -20 to +20 years from today leaving weekends and
holidays out. I'll share the code if you write to billh@gci.net,
although it may take a few hours for me to extract it for you.


On 27 Feb 2006 06:05:46 -0800, "Jannick"
<jannickrosengaardjakobsen@hotmail.com> wrote:
[color=blue]
>Help..please!
>
>I'm a rookie in terms of VB, but I'm desperately trying to make Access
>count the number of days between two dates and then subtract the
>weekends. Can anyone tell me what is wrong with the follwing code:
>
>Option Compare Database
>
>Public Function WorkingDays(StartDate As Date, EndDate As Date) As
>Integer
>
>On Error GoTo Err_WorkingDays
>
>Dim intCountA As Integer
>
>If StartDate Is Empty Then
>intCountA = 0
>Else
>
>intCountA = 0
>Do While StartDate <= EndDate
>Select Case Weekday(StartDate)
>Case Is = 1, 7
>intCountA = intCountA
>Case Is = 2, 3, 4, 5, 6
>intCountA = intCountA + 1
>End Select
>StartDate = StartDate + 1
>Loop
>
>WorkingDays = intCountA
>
>Exit_WorkingDays:
>Exit Function
>
>Err_WorkingDays:
>Select Case Err
>
>Case Else
>MsgBox Err.Description
>Resume Exit_WorkingDays
>End Select
>
>End Function
>[/color]

re: Counting Days in Access

OTTOMH
Only variants can be empty;
An EndIf is missing;
The function counts the day started, so it may not actually return the
weekdays between (I think if one inputs a Sunday and the next Saturday
as parameters one gets 6 weekdays);
The Select Case in the Err_Working Days does nothing.

I encourage you to write your own code, and will resist the temptation
to post a function of my own.