Count Work Days

[Guest]

I am trying to Count the number of work days between two dates in a query i
tryed using dhCountWorkdaysA([Date_Open],Date()) that i got form this board
but i get a err Undefined Function 'dhCountWorkdays' in expression.
C an anyone help me with this?


SELECT qryAll.[Date_Open], DateDiff("d",[Date_ Open],Date()) AS
[Days_Open], dhCountWorkdaysA([Date_ Open],Date()) AS [Work_ Days]
FROM qryAll
GROUP BY qryAll.[Date_Open],
DateDiff("d",[Date_Open],Date()),dhCountWorkdaysA([Date_Open],Date());
Octet

 

[Guest]

To use a function in a query, it needs to be a Public Function in a standard
module.

 

[Guest]

I get a Syntax error at this point in the code I copied this code fron this
board does anyon know what rhe problem is?

between #" _
' & dtmStart & "# And #" & dtmEnd & "#")



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

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
' CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate]
between #" _
' & dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

To use a function in a query, it needs to be a Public Function in a standard
module.
--
Dave Hargis, Microsoft Access MVP

I am trying to Count the number of work days between two dates in a query i
tryed using dhCountWorkdaysA([Date_Open],Date()) that i got form this board
but i get a err Undefined Function 'dhCountWorkdays' in expression.
C an anyone help me with this?


SELECT qryAll.[Date_Open], DateDiff("d",[Date_ Open],Date()) AS
[Days_Open], dhCountWorkdaysA([Date_ Open],Date()) AS [Work_ Days]
FROM qryAll
GROUP BY qryAll.[Date_Open],
DateDiff("d",[Date_Open],Date()),dhCountWorkdaysA([Date_Open],Date());
Octet

 

[Guest]

I got it but shouldent the commet be removed?

' CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate]
between #"_
' & dtmStart & "# And #" & dtmEnd & "#")

 

[Guest]

Well I finally got it to work Thanks to all.

 

[Guest]

Yes, the comment mark should be removed, sorry, I had it in a place where I
did not have the table.
As to the syntax problem in your other post.
between #" _
' & dtmStart & "# And #" & dtmEnd & "#")

Is using the line continuation mark, the underscore. On one line it should
look like this:
between "#" & dtmStart & "# And #" & dtmEnd & "#")

--
Dave Hargis, Microsoft Access MVP

I got it but shouldent the commet be removed?

' CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate]
between #"_
' & dtmStart & "# And #" & dtmEnd & "#")

I am trying to Count the number of work days between two dates in a query i
tryed using dhCountWorkdaysA([Date_Open],Date()) that i got form this board
but i get a err Undefined Function 'dhCountWorkdays' in expression.
C an anyone help me with this?


SELECT qryAll.[Date_Open], DateDiff("d",[Date_ Open],Date()) AS
[Days_Open], dhCountWorkdaysA([Date_ Open],Date()) AS [Work_ Days]
FROM qryAll
GROUP BY qryAll.[Date_Open],
DateDiff("d",[Date_Open],Date()),dhCountWorkdaysA([Date_Open],Date());
Octet