software

Help..please!

I'm a rookie in terms of VB, but I'm despretely 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
Dim intCountB As Integer

If StartDate Is Empty Then
intCountA = 0
Else

intCountA = 0
Do While StartDate lt;= 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 FunctionThis is a bit simpler

Function WorkingDays(StartDate As Date, EndDate As Date) As Long
Dim i As Long

WorkingDays = EndDate - StartDate 1
For i = StartDate To EndDate
If Weekday(i, vbMonday) gt; 5 Then
WorkingDays = WorkingDays - 1
End If
Next i

End Function

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Jannickquot; gt; wrote in message oups.com...
gt; Help..please!
gt;
gt; I'm a rookie in terms of VB, but I'm despretely trying to make Access
gt; count the number of days between two dates and then subtract the
gt; weekends. Can anyone tell me what is wrong with the follwing code:
gt;
gt; Option Compare Database
gt;
gt; Public Function WorkingDays(StartDate As Date, EndDate As Date) As
gt; Integer
gt;
gt; On Error GoTo Err_WorkingDays
gt;
gt; Dim intCountA As Integer
gt; Dim intCountB As Integer
gt;
gt; If StartDate Is Empty Then
gt; intCountA = 0
gt; Else
gt;
gt; intCountA = 0
gt; Do While StartDate lt;= EndDate
gt; Select Case Weekday(StartDate)
gt; Case Is = 1, 7
gt; intCountA = intCountA
gt; Case Is = 2, 3, 4, 5, 6
gt; intCountA = intCountA 1
gt; End Select
gt; StartDate = StartDate 1
gt; Loop
gt;
gt; WorkingDays = intCountA
gt;
gt; Exit_WorkingDays:
gt; Exit Function
gt;
gt; Err_WorkingDays:
gt; Select Case Err
gt;
gt; Case Else
gt; MsgBox Err.Description
gt; Resume Exit_WorkingDays
gt; End Select
gt;
gt; End Function
gt;
This is an Excel newsgroup, so you'd get a better response in an Access
newsgroup. There's lots of date info for Access on the following page:

www.mvps.org/access/datetime/date0012.htm

including a function to count workdays between dates.

Jannick wrote:
gt; Help..please!
gt;
gt; I'm a rookie in terms of VB, but I'm despretely trying to make Access
gt; count the number of days between two dates and then subtract the
gt; weekends. Can anyone tell me what is wrong with the follwing code:
gt;
gt; Option Compare Database
gt;
gt; Public Function WorkingDays(StartDate As Date, EndDate As Date) As
gt; Integer
gt;
gt; On Error GoTo Err_WorkingDays
gt;
gt; Dim intCountA As Integer
gt; Dim intCountB As Integer
gt;
gt; If StartDate Is Empty Then
gt; intCountA = 0
gt; Else
gt;
gt; intCountA = 0
gt; Do While StartDate lt;= EndDate
gt; Select Case Weekday(StartDate)
gt; Case Is = 1, 7
gt; intCountA = intCountA
gt; Case Is = 2, 3, 4, 5, 6
gt; intCountA = intCountA 1
gt; End Select
gt; StartDate = StartDate 1
gt; Loop
gt;
gt; WorkingDays = intCountA
gt;
gt; Exit_WorkingDays:
gt; Exit Function
gt;
gt; Err_WorkingDays:
gt; Select Case Err
gt;
gt; Case Else
gt; MsgBox Err.Description
gt; Resume Exit_WorkingDays
gt; End Select
gt;
gt; End Function
gt;--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.html