Calculate No. Days between two dates

[MikeT]

Hi,

I am using Access 2007, however and would like to calculate the number of
days between two dates.

I have the following function that i have saved to a module:



Function CalcWorkdays(StartDate, EndDate) As Integer

Dim LTotalDays As Integer
Dim LSaturdays As Integer
Dim LSundays As Integer

On Error GoTo Err_Execute

CalcWorkdays = 0

If IsDate(StartDate) And IsDate(EndDate) Then
If EndDate <= StartDate Then
CalcWorkdays = 0
Else
LTotalDays = DateDiff("d", StartDate - 1, EndDate)
LSaturdays = DateDiff("ww", StartDate - 1, EndDate, 7)
LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)

'Workdays is the elapsed days excluding Saturdays and Sundays
CalcWorkdays = LTotalDays - LSaturdays - LSundays

End If
End If

Exit Function

Err_Execute:
'If error occurs, return 0
CalcWorkdays = 0

End Function

I am trying to pull the module through my query and am using the following
expression

Expr1: CalcWorkdays(#01/01/2009#,#31/12/2009#)

however when i run the query i get a dialog box with the following:

Undefined function 'CalcWorkdays' in expression

I'm not sure what i'm doing wrong? My table is set up as StartDate and
EndDate, which i originally included in the query but this did not work
either. I'm sure this is a simple oversight, but I can't seem to see it
myself?

 

[Douglas J. Steele]

Did you save it in a stand-alone module, or in a class module (or a module
associated with a form or report)?

It needs to be a stand-alone module to be accessible from a query.

 

[MikeT]

Hi,

I just copied and pasted into a module and saved. My query does contain
table items, but the module is not connected to the table directly. At the
moment i am just in the testing process, so just working with minimal
criteria. Which i have StartDate and EndDate Name and ID. I have input some
dates on to the table as samples and set up a query based on the table.

 

[MikeT]

Hi,

I've worked it out now... and works as it should... is there a way i can
include English bank holidays into this module?

Thanks

 

[Ron2006]

You will need a table of English holidays (the actual week day that
will be taken off as opposed to the week end date)

Then create a query that counts the number of dates inthat table that
are between your dates.

then change calculation to something like:

LTotalDays = DateDiff("d", StartDate - 1, EndDate)
LSaturdays = DateDiff("ww", StartDate - 1, EndDate,
7)
LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)
LHolidays = dCount("*", "Name of Query that has
criteria - between StartDate-1 and EndDate")

'Workdays is the elapsed days excluding Saturdays and
Sundays
' CalcWorkdays = LTotalDays - LSaturdays - LSundays

CalcWorkdays = LTotalDays - LSaturdays - LSundays -
LHolidays
=============================

And add procedures for someone to maintain that Holiday date table.

Ron