none
Using a module in ACCESS 2010 RRS feed

  • 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?
    Monday, March 6, 2017 9:55 AM

All replies

  • 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

    Monday, March 6, 2017 12:14 PM
  • 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

    Monday, March 6, 2017 12:25 PM
  • 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.

    Tuesday, March 7, 2017 9:16 AM
  • Thanks ken I will give that a try.
    Tuesday, March 7, 2017 9:16 AM
  • 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

    

    Tuesday, March 7, 2017 10:37 AM
  • 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


    Tuesday, March 7, 2017 3:19 PM
  • 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


    Wednesday, March 8, 2017 10:39 AM
  • 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



    Wednesday, March 8, 2017 10:46 AM
  • 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


    Wednesday, March 8, 2017 12:12 PM
  • 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

    Wednesday, March 8, 2017 3:41 PM
  • 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

    Wednesday, March 8, 2017 4:06 PM
  • Thankyou ....again
    Thursday, March 9, 2017 5:27 PM
  • 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

    Thursday, March 9, 2017 6:05 PM
  • 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

    Friday, March 10, 2017 9:48 AM
  • 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 MSDNFSF@microsoft.com.

    Tuesday, April 4, 2017 8:34 AM
    Moderator