How to use a function in a query

[Zeunasc]

I found a query while searching the newsgroups that I think will
accomplish exactly what I want. I will include the function after I
finish this post. Anyway, the name of the function is WorkingDays2
and it's purpose is to count the number of working days from one date
to another, taking in to consideration holidays, Saturdays, Sundays,
etc. I have the issue where we work some Saturdays, but not all, so
it is very hard to do.

Anyway, I haven't ever used a function in Access before, so I am not
sure what to do with it. I know that I have to define it as a PUBLIC
function, and it goes in the Modules page. But, once I have it there,
how do I use it in a query?

If you need more info, please let me know.

TIA,
Tim

Here is the Function:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between them
' Note that this function has been modified to account for holidays.
It requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

 

[fredg]

I found a query while searching the newsgroups that I think will
accomplish exactly what I want. I will include the function after I
finish this post. Anyway, the name of the function is WorkingDays2
and it's purpose is to count the number of working days from one date
to another, taking in to consideration holidays, Saturdays, Sundays,
etc. I have the issue where we work some Saturdays, but not all, so
it is very hard to do.

Anyway, I haven't ever used a function in Access before, so I am not
sure what to do with it. I know that I have to define it as a PUBLIC
function, and it goes in the Modules page. But, once I have it there,
how do I use it in a query?

If you need more info, please let me know.

TIA,
Tim

Here is the Function:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between them
' Note that this function has been modified to account for holidays.
It requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

If you look at the first line of the function...

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer

you will notice there are two arguments (separated by a comma within
the parenthesis) that ask for specific date information., i.e.
(StartDate As Date, EndDate As Date)

That means when you call the function, you have to pass the 2 date
values to the function for it to process.

Add a new column to your query grid.

DaysWorked:WorkingDays([StartDateField],[EndDateField])

Change [StartDateField] and [EndDateField] in the query to whatever
the actual date field names are in your table.

NOTE1: The above function requires a table of holiday dates to run
properly.

NOTE2: A function placed in a Module is Public, so there is no need to
explicitly state it.