Counting Days in Access

[Jannick]

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(Sta rtDate 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(StartDa te)
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_WorkingDay s:
Exit Function

Err_WorkingDays :
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDay s
End Select

End Function

[Bill Hutchison]

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 bi***@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"
<ja************ ***********@hot mail.com> wrote:

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(Sta rtDate 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(StartDa te)
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_WorkingDa ys:
Exit Function

Err_WorkingDay s:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDay s
End Select

End Function

[Lyle Fairfield]

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.