Speeding up a function

[neemi]

I have the following code which is used quite a bit on a form to update some summary values and was wondering if anyone can help me speed this up as there are about 200 people records it goes through... Its isin't particurarly slow. prob about 2 seconds but was wondering how I can speed that up the recordset in any way.

'// calculate available resource
Dim rsP As Recordset
Dim rsT As Recordset
Dim vSDate As Date
Dim vEDate As Date
Dim vCDate As Date

Set rsP = CurrentDb.OpenRecordset("SELECT * FROM tblPerson")
Set rsT = CurrentDb.OpenRecordset("SELECT * FROM tblSummaryDetails_Temp WHERE 1 = 0")

rsP.MoveFirst
Do Until rsP.EOF
    vSDate = DateAdd("W", -4, vCurrDate)       '// First date of 1st weekending
    vEDate = DateAdd("W", 84, vCurrDate)       '// Last date of 12th weekending
    vCDate = vSDate
    Do While Format(vCDate, "YYYYMMDD") <= Format(vEDate, "YYYYMMDD")
        rsT.AddNew
            rsT![Type] = "R"
            rsT![DT_W] = "Dt_W" & fWeekNum(vCurrDate, fWeekending(vCDate)) & "_D" & Weekday(vCDate, 2)
            rsT![PersID] = rsP![PersID]
            If IsNull(rsP![sdate]) Or Format(rsP![sdate], "YYYYMMDD") <= Format(vCDate, "YYYYMMDD") Then
                If IsNull(rsP![Edate]) Or Format(rsP![Edate], "YYYYMMDD") >= Format(vCDate, "YYYYMMDD") Then
                    rsT![Available] = 8
                Else
                    rsT![Available] = 0
                End If
            Else
                rsT![Available] = 0
            End If
        rsT.Update
        Do
            vCDate = DateAdd("D", 1, vCDate)
        Loop While Weekday(vCDate, 2) = 6 Or Weekday(vCDate, 2) = 7
        
    Loop
    
    rsP.MoveNext
Loop

rsP.Close: Set rsP = Nothing
rsT.Close: Set rsT = Nothing

regards,
Neemi

 

[PHV]

Why using the Format function to compare dates ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[neemi]

I was using the format to compare the dates as when I didn't it kept using american dates for example it was reading #03/10/2008# (UK) as #10/03/2008# and when checking if this was after #14/07/2008# (UK) it was coming back as false.

ie. #14/07/2008# <= #03/10/2008# was returning false because it was reading it as ... #14/07/2008# <= #10/03/2008#

 

[Remou]

That is in SQL. I think PHV is pointing out that you are directly comparing two dates, so format should not be necessary.

 

[neemi]

OH!! I think I have always done that... so you are saying what i am doing is unnecessary and will make no difference if i comapre the dates directly?

Is that the only way you think I could speed up the function above by removing the formatted date comparison and comparing the dates directly?

But I have another similar function which passes the filters for dates where because it was swaping the dates to US I passed a formated date to the recordset, here I would need to compare the formated date I think? what do you think?

 

[Remou]

If you are comparing:

* A date in code to a date in the database, you will need format or DateSerial or such like;
* A date in the database to another date in the database, you do not need anything;
* A date in code to a date in code, you do not need anything.

I fairly sure that is right, but expect to be corrected if it is not.