working day count

[usheikh]

I am totoally new to Access VBA. I have a database that has a query which calculates the number between two dates can output this information to a report when requested. However, I want to calculate the number for working days instead so that it doesnt include weekends. I found some code:

Public Function GetNumberOfWorkDays(sStartDate, sEndDate)
Dim iDays
Dim iWorkDays
Dim sDay
Dim i

iDays = DateDiff("d", sStartDate, sEndDate)

iWorkDays = 0

For i = 0 To iDays
'First day of the week is sunday
sDay = WeekDay(DateAdd("d", i, sStartDate))
If sDay <> 1 And sDay <> 7 Then
iWorkDays = iWorkDays + 1
End If
Next
GetNumberOfWorkDays = iWorkDays
End Function

However, the problem is how do I implement this code into my database? There is no place to enter the code in the query. Do I have to replace the start date and end date names with the date field names used in my database?

Please could somebody give me some idea as I have never used Functions before? Thanks!

 

[PHV]

In the query grid:
NumberOfWorkDays: GetNumberOfWorkDays([StartDate field], [EndDate field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rubbernilly]

Put the function in a module.

Go to your database window, the modules tab/page, and add new. If you don't get taken to the VB editor, double click on the module and you will.

Put your cursor down in empty space and paste in this code.

You will see that your module now has an entry at the top for your function. At the top of the code window you will see two drop downs. One should say "General" and the other will now say the name of your function. Save the module, and make the change to your query PHV suggested.

That is the way that you use functions:

Build the function in VBA, setting it to return the proper data type for the field you wish to use it with. Your code will return a variant:

Public Function GetNumberOfWorkDays(sStartDate, sEndDate)

"as Variant" is the default. This will work with your query as long as the function always returns a number - the variant type should be able to house/translate the number value for your query. If the function errors for some reason, it might return a different data type. You can explicitly return a particular type as required by your query by changing the declaration statement of the function:

Public Function GetNumberOfWorkDays(sStartDate, sEndDate) as Integer

Integer will work as long as you stay below ~32,000 days returned by the function. Larger than that, and you might want to have it return "as Long."

Just a little crash course for you on some of the important things to know about how to implement functions in your database. Good luck!

 

[usheikh]

THANKS SO MUCH!!!

 

[MichaelRed]

More elegant (e.g. complete) function faq181-261



MichaelRed

 

[usheikh]

The function worked.

What if I want to find out the difference between a start date and todays date???

If I do,

GetNumberOfWorkDays(Date,[StartDate])

then it doesnt work. I get an error back saying "Invalid Use of Null" and in the debug window the following line is highlighted

For i = 0 To iDays

Do you know why this may be happening?

 

[PHV]

what about this ?
GetNumberOfWorkDays([StartDate],Date())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[usheikh]

No that doesnt work either. I get the same error. Its not agreeing with the code for some reason.