You are here:

Using MS Access/Access 2007 - Work Days trying your code


QUESTION: I am using your code from several other answers on the site. (This is after about 20 other NEAR solutions when finally I decided to go with an expert!)

I have a form where I put in the dates (and I can select the Manager etc.) push a button and theoretically get a report with the star dates listed (got that fine) the number of working days in the period selected (PROBLEM)

I created a table named HolDate with holidays as a field and completed that. I tried changing the control source in the report field from: =NetWorkdays([StartDate],[EndDate]) to =NetWorkDays([forms]![HomePage]![TxtStartDate],[forms]![HomePage]![TxtEndDate]) but that didn't work, I changed it to what you suggested in your post and when it requested the dates I entered them and it returned a value of '0'. I guess I should be ecstatic that it didn't say error and I have a feeling I am doing something wrong, but for the life of me I do not know what!

Please HELP!

ANSWER: First, did you create a NetworkDays() function? If so what is the code for that?

If you want this net days to appear on a report then I would put the function in a the query you base the report on. Then reference the fields in the record.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: I created a function using your suggested code on this site for the  function called NetWorkDays(), then I created a table named HolDate with a field Holidays. I then tried calling it to the report page =NetWorkdays( ) but that didn't work. I have a [Forms]![HomePage]![TxtStartDate] & the same for end date. Should I substitute that in the code somewhere? And in the calling to the report or if I put it in a query as suggested, how would I go about and would it do between the dates entered on the form when it created the report?...I am sorry I am a BSA not a VBA but I am trying, just not always succeeding!

Thank you for your assistance!

ANSWER: What is the scope here. Are you trying to find the net work days for a set of records or just between the two dates you enter on the form?

If its just the dates entered on the form, then just add a control on the form with a controlsource of:


But first I would test the function in the immediate window by entering:

? NetWorkDays(#12/15/12#,#1/15/12#)

If it returns 0 then there is something wrong with the function and I need to see the code. If it returns an incorrect value, I need to know what it returned and what it should.

If you are looking to get the days for a set of records, then put it in a query.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Okay, I am possibly quite thick & I apologize.
The code I entered is:

Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer

Dim intGrossDays As Integer
Dim dteCurrDate As Date
Dim i As Integer

intGrossDays = DateDiff("d", dteStart, dteEnd)
NetWorkdays = 0

For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbMonday) < 6 Then
  If IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & dteCurrDate & "#")) Then
      NetWorkdays = NetWorkdays + 1
  End If
End If
Next i

End Function

Part of the problem seems to be that the Function is "Not Defined". I tried to place in the form and it messed up my ability to select the dates so I removed it. Then I returned to the report and placed "=NetworkDays([TxtStartDate],[TxtEndDate])" in the control source for the field where I want it to appear. When I run the report it does seem to be calculating, but ten returns zero.

I feel like it is almost there but not quite. Is there a way to get it to recognize the function(if in fact it isn't?) I looked in VBA Editor/Tools/Reference, but wasn't sure what I was looking for!

Oh-I tried to put it in a query and it read: Undefined Function 'NetWorkDays' in expression.

I hope I am not driving you crazy with my lack of understanding.

Code looks good, but this code should go in a Global Module, not in the form module.

Open the VBE (Visual Basic Editor) and either create a new Module or past the code into an existing module.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.