Code:
SELECT Nz(Count([tblDeclaredHolidays]![DecHolidayReason]),0) AS Holidays
You don't need the Nz Function call there. Since you're returning a count of the holidays already, it will already return zero if it doesn't find any.
Code:
InterveningHolidays = rst.RecordCount
Since your SQL Query is just returning the number of holidays, it will ALWAYS return a single Record (even if the number of holidays is zero).
I don't know your exact database structure, but try this function out. . .
Code:
Option Compare Database
Option Explicit
Public Function InterveningHolidays(StipulatedDate As Date, DeliveryDate As Date) As Integer
Dim rst As DAO.Recordset
Dim strSQL As String
' set default return value
InterveningHolidays = 0
strSQL = "SELECT Count([DecHolidayReason]) AS Holidays " & _
"FROM tblDeclaredHolidays " & _
"WHERE [DecHolidayDate] BETWEEN #" & StipulatedDate & "# AND #" & DeliveryDate & "#"
' open recordset
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
InterveningHolidays = rst("Holidays")
' clean up
rst.Close
Set rst = Nothing
End Function