Originally Posted by Gasman
Galaxiom: It is my function that was slow. Query for the rest of the data appears to be fine.
Stepping through recordsets is always going to be slower than a query.
Applying a function that opens a recordset for each employee will be very slow. Using a Domain function will still be slow. Try to do it by joining the tables so the engine can do its group processing. Remember the SQL View can support joins that can't be done in the Design View.
Wherever it is done, applying a function, any function, to every record before the Select can be applied will slow a query.
Public Function NewHolidaysRemaining(pEmployeeID As Long, pDate As Date)
strCriteria = "EmployeeID = " & pEmployeeID & " AND YEAR([StartDate])= " & Year(pDate) & " AND YEAR([EndDate])= " & Year(pDate)
'strCriteria = "EmployeeID = " & pEmployeeID & " AND [StartDate]>= #" & dtStart & "# AND [EndDate]<= #" & dtEnd & "#"
'strSQL = strSQL & " WHERE (((Year([StartDate]))=" & Year(pDate) & ") AND ((Year([EndDate]))=" & Year(pDate) & ")"