MS Access 2003 - Undefined Function

Fuzzytrooper

Hi all, I've written a small bit of VB code to calculate the number of working days between two dates. I get the message "Undefined Function ' WorkDays2' in expression".

I've tried a few tricks, checked for missing references, added and then removed a random reference. Any thoughts?

Public Function WorkDays2(dteStart As Date, dteEnd As Date) As Long

WorkDays2 = 0

If dteStart <> 31 / 12 / 9999 Then
    Do While dteStart <> dteEnd
        dteStart = DateAdd("d", 1, dteStart)
        If Weekday(dteStart, vbMonday) <= 5 Then
            WorkDays2 = WorkDays2 + 1
        End If
    Loop
End If
            
End Function

Dr.Silly

have you tried defining what workdays2 is

something like

dim workdays2 as int
set workdays 2 = 0

Fuzzytrooper

WorkDays2 is the function name and hence the return type. I just double checked as you suggested but this threw up a compile error (Duplicate declaration)

Dr.Silly

this jsut worked for me, just did a button with a message box calling the function.
So you're code works fine, I imagine it's the date format which you're passing in.

do
msgbox date
and see what format your date comes out as, should work fine.


Private Sub Command1_Click()
MsgBox WorkDays2("23/09/2010", "24/09/2010")


End Sub
Public Function WorkDays2(dteStart As Date, dteEnd As Date) As Long

WorkDays2 = 0

If dteStart <> 31 / 12 / 9999 Then
Do While dteStart <> dteEnd
dteStart = DateAdd("d", 1, dteStart)
If Weekday(dteStart, vbMonday) <= 5 Then
WorkDays2 = WorkDays2 + 1
End If
Loop
End If

End Function

dnme

I'd refactor it like this, use a dedicated variable in your calculation, especially as it's used in a loop

Public Function WorkDays2(dteStart As Date, dteEnd As Date) As Long

Dim ret as intreger
ret = 0

If dteStart <> 31 / 12 / 9999 Then
Do While dteStart <> dteEnd
dteStart = DateAdd("d", 1, dteStart)
If Weekday(dteStart, vbMonday) <= 5 Then
ret = ret + 1
End If
Loop
End If

WorkDays2 = ret

End Function