Counting Workdays Function

[Guest]

When i try to call the fuction i get a error I am using
CalcWorkDays([DateDiverted],[dtmEnd])

The error says The expression is typed incorrectly or it too comples to be
evaluated

Does anyone know what wrong this I am a beginner VB

Function CalcWorkDays(DateDiverted As Date, dtmEnd As Date) As Integer

'Calculated the number of working days between two dates
'DateDiverted - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", DateDiverted, dtnEnd) + 1 'Start
with total days
'Add one
to include
First Day
dtmToday = DateDiverted
'Initiate compare date
Do Until DateDiverted > dtmEnd
If WeekDay(dtmToday, vbMonday) > 5 Then 'It is Saturday or
SUNDAY
intTotalDays = intTotalDays - 1 'Take one day
away for Weekend day
ElseIf Not IsNull(DLookup("[HolidayDate]", "tblHolidays", _
"[HolidayDate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 ‘Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
Compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

Thanks

Octet

 

[Michel Walsh]

What is the context ? You are inside the query designer? Your function is
defined inside a standard module, NOT under a Form neither a Report neither
a Class.

The error occur when you try to save the query (if so, you may have a
problem of parenthesis, or other syntax problem), or when you try to see
data? It is possible, then, you have no field called DateDiverted, or
called dtmEnd (although you should get a dialog for entering a parameter if
this is the case) ? Is it possible one of these field has a NULL value? if
so, your function declaration should be:

Function CalcWorkDays(DateDiverted As VARIANT, dtmEnd As VARIANT) As
Integer


and your function should handle the possible case of the arguments being
null.


Vanderghast, Access MVP