I did not see what error you are getting.
Also, be sure the module name is not the same as the function name. That is
not allowed.
--
Dave Hargis, Microsoft Access MVP
:
It is not, I tried creating a new one several times, still same error...
So you mean it is working now?
--
Dave Hargis, Microsoft Access MVP
:
I just created a new module, pasted the code, went to properties window and
changed the name. That's it.
Okay, where did you put the code?
--
Dave Hargis, Microsoft Access MVP
:
Sorry, I don't know how to find out what you are asking?
Do you have the function in a standard module and is it designated as a
Public function?
--
Dave Hargis, Microsoft Access MVP
:
I'm all of a sudden getting this error when I open the query, any idea why?
Haven't change anything!
Undefined function 'CalcWorkDays' in expression
Curtis
Opps! I think that was my fault. Sorry
But, glad it is working for you.
--
Dave Hargis, Microsoft Access MVP
:
Date() needed to be second or last...
Outstanding Days: CalcWorkDays(Date(), [Customers]![SentInDate])
It works.
Thanks!!!
Curtis
I don't know what to tell you, Curtis. It works as designed for me. Are
there any holidays that fall between the two dates?
Here it is again:
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
On Error GoTo CalcWorkDays_Error
'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")
CalcWorkDays_Exit:
On Error Resume Next
Exit Function
CalcWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
End Function
--
Dave Hargis, Microsoft Access MVP
:
When I did that, it shows -1 and it didn't show 2, but 0. I simply copied &
pasted your coding and created a blank table called holidays
Curtis
Actually, it returns 2
11/30/2007 is 1 workday
12/1/2007 and 12/2/2007 are both weekend days.
12/3/2007 is 1 workday.
If you want it to show 1 day's difference, then change this line:
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
to
DateDiff("ww", dtmStart, dtmEnd, 1))
As to how you are getting zero, I can't tell.
--
Dave Hargis, Microsoft Access MVP
:
Thanks, this totally works, EXCEPT for the one in my example, it now says 0
instead of 1?
Thanks
Curtis
Here is a function you can use in your query that will calculate the number
of days excluding week ends and holidays. You will need a holiday table set
up, however.
You would call it like this:
Outstanding Days: CalcWorkDays(Date(), [Customers]![SentInDate])
Put the function in a standard module so the query can see it.
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
On Error GoTo CalcWorkDays_Error
'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")
CalcWorkDays_Exit:
On Error Resume Next
Exit Function
CalcWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
End Function
--
Dave Hargis, Microsoft Access MVP
:
I have a query with this function as the field:
Outstanding Days: Date()-[Customers]![SentInDate]
The SentInDate field in the Customer's table is a date that represents when
the order was placed. The field in this query is suppose to show how many
outstanding days have passed for this open order.
BUT I don't want it to calculate/include weekends (sat/sun), just weekdays.
So if it was placed on Friday, November 30th, it would say 1 instead of 3
today!
Thanks!
Curtis
