Function to calculate elapsed days minus weekends/holidays gives Name? error

[sleake]

Question 1: Found code on MVP site to perform this calculation. I want it to calculate from a control named dteToPQA, exclude holidays in tblHolidays, field HolidayDate, using the internal Date() function as the "EndDate". I've substituted my control name, but am getting a Name? error. Where I have Date(), the author had the term EndDate. Maybe I expressed the Date() incorrectly. Here's the code.

 Option Compare Database
Public Function WorkingDaysToPQA(dteToPQA As Date, EndDate As Date) As Integer
'....................................................................
' Name:     WorkingDaysToPQA
' Inputs:   dteToPQA As Date
'       Date() As Date
' Returns: Integer
' Author: Arvin Meyer
' Date:     May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDaysToPQA
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
'dteToPQA = dteToPQA + 1
'To count dteToPQA as the 1st day comment out the line above
intCount = 0
Do While dteToPQA <= Date
rst.FindFirst "[HolidayDate] = #" & dteToPQA & "#"
If Weekday(dteToPQA) <> vbSunday And Weekday(dteToPQA) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If
dteToPQA = dteToPQA + 1
Loop
WorkingDaysToPQA = intCount
Exit_WorkingDaysToPQA:
Exit Function
Err_WorkingDaysToPQA:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDaysToPQA
End Select
End Function

Question 2: I want to use this function as part of a Source Data expression for another unbound control on the form. But, I want to insert it into an IIf statement: IIf([txtStatus] = "In process", WorkingDaysToPQA, ""). Is this the correct way to get the results I want -- the calculation only for records "In process"?


Will appreciate suggestions.

[pbaldy]

The function as written expects the end date to be passed to it:

Public Function WorkingDaysToPQA(dteToPQA As Date, EndDate As Date) As Integer

If you don't want to do that, delete the part in red. You can leave it, but you still have to pass a date there. You could leave the function as written and pass Date() too.

[rpeare]

I don't see anything obviously wrong with the code at a quick glance, i'll go test it elsewhere but I am assuming you want the value of workingdaystoPQA to be displayed on a form. if that's the case you want some event driven code more like

If([txtstatus] = "In Process" Then
    [Display Field] = workingdaystoPQA(date1, date2)
else
    [display field] = null
endif

[sleake]

The EndDate is today's date. Sorry to be so uninformed, but how do I write that? "(dteToPQA As Date, EndDate As Date, Date() As Date)"?

[sleake]

Yes on a form and also in a report. So this would go in the On Current or some other event of the control?

[pbaldy]

Rather than try to modify a function you don't understand, just pass the current date, like:

IIf([txtStatus] = "In process", WorkingDaysToPQA(YourDateField, Date()), "")