Using a module in ACCESS 2010

Question

I have copied a module which will allow me to calculate only the working week, excluding weekends.  How do I get the module to work so that the answer can be displayed as a field in a report?

Answer 1

You'll need to insert a textbox and then build an expression using the functions from your module, but without knowing which functions you are using our anything about your database (fields, data) it is very hard to guide you.  Perhaps you could elaborate on what you have and what you are looking to get as results and we will be in a better position to help.

---

Daniel Pineault, 2010-2016 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

The module will presumably contain one or more functions into which one or more values can be passed as its arguments, on the basis of which the function will return a value.  In a bound form, therefore, you will usually call the function in an expression as the ControlSource property of an unbound text box, passing a value or values, e.g. in another control or controls on the form, as the arguments.  The function might also be called in the same way in a computed column in a query on which the form is based, in which case a text box can be bound to the computed column.

In an unbound dialogue form you might call the function in code in the form's module, e.g. the Click event of a button, to assign the return value of the function to another control in the form.  You'll find examples of this in Workdays.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes various functions for workday calculations, taking into account weekends and public holidays, both in the context of a single country or in a multi-country context, as here in the UK where each of the constituent countries have different public holidays.

---

Ken Sheridan, Stafford, England

Answer 3

Daniel

This is the module:

          

Function Work_Days(StartDate As Variant, EndDate As Variant) As Integer

 Dim WholeWeeks As Variant
 Dim DateCnt As Variant
 Dim EndDays As Integer

 On Error GoTo Err_Work_Days

 StartDate = DateValue(StartDate)
 EndDate = DateValue(EndDate)
 WholeWeeks = DateDiff("w", StartDate, EndDate)
 DateCnt = DateAdd("ww", WholeWeeks, StartDate)
 EndDays = 0

 Do While DateCnt <= EndDate
 If Format(DateCnt, "ddd") <> "Sun" And _
 Format(DateCnt, "ddd") <> "Sat" Then
 EndDays = EndDays + 1
 End If
 DateCnt = DateAdd("d", 1, DateCnt)
 Loop

 Work_Days = WholeWeeks * 5 + EndDays

Exit Function

Err_Work_Days:

 ' If either StartDate or EndDate is Null, return a zero
 ' to indicate that no workdays passed between the two dates.

 If Err.Number = 94 Then
 Work_Days = 0
 Exit Function
 Else
' If some other error occurs, provide a message.
 MsgBox "Error " & Err.Number & ": " & Err.Description
 End If

End Function

We have a personnel report and want to filter out the weekends.  We use a parameter in the query behind the report to select the individual and the startdate and enddate for the period we are trying to check.  We currently include a manual text field where we add up the actual number of worked days during the period.  Kind regards and thanks in advance.

Answer 4

Thanks ken I will give that a try.

Answer 5

Daniel

This is the query behind the report we are using, using two tables: Personnel Details and Absence

SELECT [Personnel Details].[Permanent Employee?], Absence.[Pay No], [Personnel Details].[First Name], [Personnel Details].Initial, [Personnel Details].Surname, Absence.StartDate, Absence.Year, Absence.EndDate, Absence.[No of Days Absent], Absence.[Reason for Absence], Absence.Comments
FROM [Personnel Details] INNER JOIN Absence ON [Personnel Details].[Pay No] = Absence.[Pay No]
WHERE ((([Personnel Details].[Permanent Employee?])=True) AND ((Absence.[Pay No]) Like [Type in Payroll Number <Return> for ALL] & "*") AND ((Absence.StartDate) Between [Enter Start Date dd/mm/yy] And [Enter Return Date dd/mm/yy]));

Currently the number of days absent has to be entered manually in the form to be displayed in the report



Answer 6

Post the SQL for your report's recordsource query and the module here so we can see what you have. Usually it would be something like:

SELECT NameOfFunction([myField]) as NumberOfDays
FROM myTable

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 7

Bill

This is the report SQL Query which shows any absence periods but requires a manual calculation to determine the actual lost sick days excluding weekends.  

SELECT [Personnel Details].Spc, Absence.[Pay No], Absence.Year, [Personnel Details].[First Name], [Personnel Details].Initial, [Personnel Details].Surname, Absence.StartDate, Absence.EndDate, Absence.[No of Days Absent], Absence.[Reason for Absence], Absence.[Sickness Type], Absence.[Certificate Type], Absence.[Certificate Expiry Date], Absence.[Accident on Duty], Absence.[Accident Date], Absence.[Accident Report Received], Absence.[Accident Report Ref], Absence.[Work Action Code], Absence.[Welfare Case], Absence.[Date Referred to OHS], Absence.Comments, Absence.[Sick Days YTD - Days], Absence.[Sick Days 4 YTD - Days], Absence.[Asbestos Exposure]
FROM [Personnel Details] INNER JOIN Absence ON [Personnel Details].[Pay No] = Absence.[Pay No]
WHERE (((Absence.[Pay No]) Like [Type in Pay Number: <RETURN> for ALL] & "*") AND ((Absence.Year)=[Type in Year:]));

and the proposed module is one from MIcrosoft

          

Function Work_Days(StartDate As Variant, EndDate As Variant) As Integer

 Dim WholeWeeks As Variant
 Dim DateCnt As Variant
 Dim EndDays As Integer

 On Error GoTo Err_Work_Days

 StartDate = DateValue(StartDate)
 EndDate = DateValue(EndDate)
 WholeWeeks = DateDiff("w", StartDate, EndDate)
 DateCnt = DateAdd("ww", WholeWeeks, StartDate)
 EndDays = 0

 Do While DateCnt <= EndDate
 If Format(DateCnt, "ddd") <> "Sun" And _
 Format(DateCnt, "ddd") <> "Sat" Then
 EndDays = EndDays + 1
 End If
 DateCnt = DateAdd("d", 1, DateCnt)
 Loop

 Work_Days = WholeWeeks * 5 + EndDays

Exit Function

Err_Work_Days:

 ' If either StartDate or EndDate is Null, return a zero
 ' to indicate that no workdays passed between the two dates.

 If Err.Number = 94 Then
 Work_Days = 0
 Exit Function
 Else
' If some other error occurs, provide a message.
 MsgBox "Error " & Err.Number & ": " & Err.Description
 End If

End Function

Answer 8

Ken

That is simple and brilliant.  Only trouble is, I don't know how to build the functionality into my database.

One of the reports we are using, to determine how many days are off sick, uses the following SQL query.  This requires a manual entry on the form to calculate the days excluding the weekends.

SELECT [Personnel Details].Spc, Absence.[Pay No], Absence.Year, [Personnel Details].[First Name], [Personnel Details].Initial, [Personnel Details].Surname, Absence.StartDate, Absence.EndDate, Absence.[No of Days Absent], Absence.[Reason for Absence], Absence.[Sickness Type], Absence.[Certificate Type], Absence.[Certificate Expiry Date], Absence.[Accident on Duty], Absence.[Accident Date], Absence.[Accident Report Received], Absence.[Accident Report Ref], Absence.[Work Action Code], Absence.[Welfare Case], Absence.[Date Referred to OHS], Absence.Comments, Absence.[Sick Days YTD - Days], Absence.[Sick Days 4 YTD - Days], Absence.[Asbestos Exposure]
FROM [Personnel Details] INNER JOIN Absence ON [Personnel Details].[Pay No] = Absence.[Pay No]
WHERE (((Absence.[Pay No]) Like [Type in Pay Number: <RETURN> for ALL] & "*") AND ((Absence.Year)=[Type in Year:]));

 I hope this makes sense??

LBAlan

Answer 9

The first thing I should make clear is that my function and that which you posted do different things.  Mine operates like the built in DateDiff function, but excludes working days and, optionally, public holidays, from the calculation, i.e. it is a subtractive operation.  As far as I can see from a cursory examination, your function counts the working days within a range.  The results of each might not be the same, therefore.  Your function does not include any provision for excluding public holidays from the count, however, but it would be a simple task to amend it to do so.

With your function, to return the number of working days absent you'd merely call the function as a computed column in the query, e.g.

    Work_Days(Absence.StartDate, Absence.EndDate) AS WorkDaysAbsent

I'm rather concerned about your [Sick Days YTD - Days] and [Sick Days 4 YTD - Days] columns.  These suggest you are storing computed values in columns in the table, which would put it at risk of update anomalies.  Cumulative values like this should be computed at runtime and returned in a computed column in the query, not stored in a column in the base table.  There is then no risk of the value being inconsistent with the values on which the computation is based.

---

Ken Sheridan, Stafford, England

Answer 10

Ken

The query does not like the 'AS' part of the computed column and will not allow the query to run.

Yes it would be nice to be able to exclude public holidays.

With regard to the YTD fields, these are only used in a summary report as totals and are actually blank in the actual table.  I just need to remove the fields from the table

Kind regards

Answer 11

I'm guessing you might have tried to add the computed column in query design view rather than SQL view.  In design view you'd put the following in the 'field' row of a blank column in the design grid:

WorkDaysAbsent: Work_Days(Absence.StartDate, Absence.EndDate)

---

Ken Sheridan, Stafford, England

Answer 12

Thankyou ....again

Answer 13

You might be interested to know that I've added options to the Workdays demo in my OneDrive folder for counting the number of working days in a range.  The solutions use very little code as, rather than computing the count in code, it simply looks up the number of relevant rows in an auxiliary calendar table of all weekday dates (from 1900 to 2100), in conjunction with a table of public/concessionary holidays.  As well as being a simple solution it's also a very efficient one, as it's just a single set operation.

---

Ken Sheridan, Stafford, England

Answer 14

Ken

A big thank you.  The report now works with the work_days field.

I will look at the new working day database option that you have now updated.

Kind regards

Alan Smith

Answer 15

Hi LBAlan,

From your last post , it looks like your issue is solved.

but you did not mark an answer.

so this thread is still open.

please try to mark the suggestion given by Ken Sheridan as an answer.

so we can close this thread.

if you do not mark the answer then this thread will remain open forever.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].