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.
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.