Calculating workdays... and previous threads

[vangogh228]

Hello.

I've been trying very hard to get through the various instructions, code, modules, etc, on calculating workdays. I am having no luck in getting this done. As one who can write an Access database only to the point of a little VBA, using mostly queries and formulae to get by, I have difficulty with posted code from time to time.

For example, in a previous thread (actually, in several previous threads), code was posted with the instruction (essentially), "Here. Use this." To which someone replies, "Thanks, worked a charm." Or the like. As one who struggles and has limited responsibilities in this area (and little time to learn while on the job), posted code can be a blessing... but "Here. Use this." can be its attached curse. "HOW do I use it?" is the next question. Is it a module? Do I put it as code on the On Open event for a form? Is it an expression I build into a query? Sometimes, seeing the code is only the first step... and for one like me, can lead to a lot of trouble.

Also, many have been kind enough to post databases, which show the end result of much work. This is also greatly appreciated, believe me. But, again, once I start looking to HOW it was built... the investigation sometimes leads nowhere. Where do I look for code? Did I miss something? Why doesn't mine work like that?

So, for the question I have now, I have searched this and other forums. I have copied and pasted code that I thought should work. I have copied code into a module and tried to call up that module in a query. I have followed instructions as well as I could... but you can guess where this is headed. What I have is, in the simplest form, a table called Employees with the fields Name, StartDate and EndDate. I need to know how many workdays there are in the date range of StartDate to EndDate, including the dates in those fields.

If you have answered this question before, I apologize for not being able to figure this out. Any help is greatly appreciated.

 

[MStCyr]

Create a new module and paste the following code into it.

If you have any problems please feel free to post back.


Public Function mlfWorkingDays(BeginDate As Date, EndDate As Date) As Integer

'Returns # of whole working days (M-F) between 2 dates.
'assumptions:
' Working days are M-F
' Day started and Day ended are counted as full days.
' tblHolidays contains only holidays that fall on M-F.
' The BeginDate must fall on/before the EndDate.


Dim intDays As Integer

If BeginDate > EndDate Then
mlfWorkingDays = 0
Exit Function
End If
'There are 5 steps involved in calculating the # of working days:
intDays = DateDiff("d", BeginDate, EndDate) + 1 'Total days
intDays = intDays - 2 * DateDiff("ww", BeginDate, EndDate) 'Sub full weekend days
intDays = intDays + (WeekDay(BeginDate) = vbSunday) 'Adjust if start on Sunday
intDays = intDays + (WeekDay(EndDate) = vbSaturday) 'Adjust if end on Saturday

'If you have a tblHolidays (or equivalent), you use this code to adjust for workday holidays.
' intDays = intDays - DCount("*", "tblHolidays", _ 'Adjust for holidays
' "Holiday BETWEEN " & BeginDate & " AND " & EndDate)
mlfWorkingDays = intDays
End Function

 

[vangogh228]

Thanks. I have seen similar modules as well. Once I have created the module... then what do I do? I am assuming I write a query or create a form in which I can refer to the module... but have no experience in the syntax to make hte reference to it. Thanks again for your help.

 

[MStCyr]

Thanks. I have seen similar modules as well. Once I have created the module... then what do I do? I am assuming I write a query or create a form in which I can refer to the module... but have no experience in the syntax to make hte reference to it. Thanks again for your help.

Yes you would create a form on which you would enter the StartDate and the EndDate. Then the StartDate and EndDate of your function would be called from the form...ie. forms!myform!startdate

 

[MStCyr]

if you wish I can make up a sample of this for you..

Maurice

 

[MStCyr]

Yes you would create a form on which you would enter the StartDate and the EndDate. Then the StartDate and EndDate of your function would be called from the form...ie. forms!myform!startdate

Hi

I created a small sample for you over the lunch hour. If you need additional help please let me know.

 

[vangogh228]

THANKS so far.

I am working through this... slowly. Thanks for all your help so far.

So, make sure I understand... I have the module that calculates mlfWorkingDays. I can base my form on my table that has StartDate and EndDate in it. In the form, I can have the WorkingDays control. Then, I would put the calculation of Me!WorkingDays = mlfWorkingDays as an On Open and On Current event, plus as an After Update event on the StartDate and EndDate controls. Is that correct?

Whoooooo-boy... this is stretching me a bit here. Can't express my thanks enough.

---

Now... if all that is right... can I also call this up in a query, leading to a report?

 

[MStCyr]

I am working through this... slowly. Thanks for all your help so far.

So, make sure I understand... I have the module that calculates mlfWorkingDays. I can base my form on my table that has StartDate and EndDate in it. In the form, I can have the WorkingDays control. Then, I would put the calculation of Me!WorkingDays = mlfWorkingDays as an On Open and On Current event, plus as an After Update event on the StartDate and EndDate controls. Is that correct?

Whoooooo-boy... this is stretching me a bit here. Can't express my thanks enough.

---

Now... if all that is right... can I also call this up in a query, leading to a report?

As per the sample I provided you can calculate the working days as long as you have both a Start Date and an End Date. Typically the calculation would be done on the On Current event, if you do the calculation on the After Update event, which means that you have changed a date, then you should refresh the form ( Me.Refresh) to make sure the date gets stored in the table.

You can certainly call this up in a query, however I would call the function in the report itself, i.e. create a new field and then call up the function.

Where is Canton, Ohio? .... I have a good friend that lives in Cambridge, Ohio

Best regards

Maurice