Thread: [SOLVED] Speed this up at all?
View Single Post
Old 07-18-2017, 01:34 AM   #10
Access newbie
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,134
Thanks: 268
Thanked 317 Times in 302 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Speed this up at all?

I've amended the code to calculate the holidays to a simple DSum instead of the recordset. I've left everything in so you can see what I was originally using.
I also tried using a date, rather than Year of a date, but that seemed slower.

That has at least made it a little faster to display the holidays remaining.

I am not sure if I even have to recalculate each time I move dates, as the names remain static and I would only really need to calculate after holidays have been added/deleted or amended, but will have to take some time getting to understand it again.

For now it is sufficient for my needs..

CJ_London: I also indesed the start and end dates in the tblHolidayDates.

The _Doc_Man: Just that there is a noticable pause before the calculated days are displayed, when the images are pretty much instant, but they come from a query on a single table.

static: It was hard enough for me to modify for the holidays, so will leave the images as they are for now.

Galaxiom: It is my function that was slow. Query for the rest of the data appears to be fine.

Thanks everyone.

Public Function NewHolidaysRemaining(pEmployeeID As Long, pDate As Date)
On Error GoTo Err_Handler

Dim iHolidaysAllowed As Integer, iHolidaysUsed As Integer
Dim strSQL As String, strCriteria As String
Dim db As Database
Dim rst As Recordset
'Dim dtStart As Date, dtEnd As Date

Set db = CurrentDb()
iHolidaysAllowed = Nz(DLookup("AllowedHolidays", "tblEmployees", "[EmployeeID]=" & pEmployeeID), 0)
'iHolidaysUsed = Nz(DSum("Holidays", "tblHolidayDates", "[EmployeeID]=" & pEmployeeID & " AND [HolidayType]=5"), 0)
'dtStart = DateSerial(Year(pDate), 1, 1)
'dtEnd = DateSerial(Year(pDate), 12, 31)

strCriteria = "EmployeeID = " & pEmployeeID & " AND YEAR([StartDate])= " & Year(pDate) & " AND YEAR([EndDate])= " & Year(pDate)
'strCriteria = "EmployeeID = " & pEmployeeID & " AND [StartDate]>= #" & dtStart & "# AND [EndDate]<= #" & dtEnd & "#"

iHolidaysUsed = Nz(DSum("Holidays", "tblHolidaydates", strCriteria), 0)

'strSQL = "SELECT Workdays([StartDate],[Enddate],'tblPublicHols','HolidayDate') AS HolsBooked, tblHolidayDates.EmployeeID, tblHolidayDates.StartDate, tblHolidayDates.EndDate "
'strSQL = strSQL & " FROM tblHolidayDates"
'strSQL = strSQL & " WHERE (((Year([StartDate]))=" & Year(pDate) & ") AND ((Year([EndDate]))=" & Year(pDate) & ")"
'strSQL = strSQL & " GROUP BY tblHolidayDates.EmployeeID"
'strSQL = strSQL & " HAVING ((tblHolidayDates.EmployeeID)= " & pEmployeeID & "))"

'Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
'If Not rst.EOF Then
    'iHolidaysUsed = 0
   ' Do While Not rst.EOF
        'iHolidaysUsed = iHolidaysUsed + rst!HolsBooked
       ' rst.MoveNext
'End If

NewHolidaysRemaining = iHolidaysAllowed - iHolidaysUsed

    Set db = Nothing
    Set rst = Nothing

    Exit Function
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume ExitFunction

End Function
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote