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

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" <jannickrosengaardjakobsen@hotmail.com> wrote in message
news:1141048384.251476.158190@j33g2000cwa.googlegroups.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
>

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