Visual Basic programming
 

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

Visual Basic programming
 

This 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) 5 Then
WorkingDays = WorkingDays - 1
End If
Next i

End Function

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jannick" wrote in message
oups.com...
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 <= 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

Visual Basic programming
 

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:

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

including a function to count workdays between dates.

Jannick wrote:
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 <= 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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html