Iterate over a table and update matching recordset - Access - Vba

Question

Issue

I built a tracking system with unique workID and Ids that come from other tables. I have a column called multiplier that I will like to change if some conditions are met. I will like to change the multiplier column for every record where: the date is Sunday and is preceded by Saturday (meaning the date of the Saturday needs to be in the table) and has the same ID as the Saturday.

Basically this is a bonus system where if a runner worked on a Saturday and a Sunday, then on that Sunday he gets a multiplier of 2. I open recordset for every sunday and saturday present in the table and try to match them. But it skips some stuff because the saturday is not always followed by a sunday.

Code

sqlFindSat = "Select WorkID, Date, Task, ID, Multiplier from tblA where  (weekday([date]) = 7) order by date, id, workId"
sqlFindSun = "select WorkID, Date, Task,  ID, Multiplier from tblA where ( weekday([date]) = 1 ) order by date, id, workId"


Set fwSun = db.OpenRecordset(sqlFindSun)
Set fwSat = db.OpenRecordset(sqlFindSat)

Do While Not fwSat.EOF
    waitforsat = False
    debug.print " Curr Sat: " & fwSat!Date & " Curr Sun: " & fwSun!Date

    If WeekdayName(Weekday(fwSat!Date)) = "saturday" Then
       If ((DateAdd("d", 1, fwSat!Date) = fwSun!Date) And (fwSun!ID = fwSat!ID)) Then
            fwSun.Edit
            fwSun!multiplier = 2
            fwSun.Update
       End If

    End If

    If Not fwSun.EOF Then
        fwSun.MoveNext
    End If

    fwSat.MoveNext
Loop

My code does this fine as long as the saturday is followed by a sunday. As soon as one saturday is not followed by a sunday, then the cursors are at different levels sometimes one is. I don't mind doing this in SQL Update or anything else as long as it works in access.

Note: the sample below isn't real sample. The comment section is to show what day of the week it is.

Sample table tblA

Workid |    Date     | ID | Multiplier | comments 

  12     10-11-2012    2       1           sunday

  13     15-12-2012     4      1           Monday

  14     10-10-2012    3       1           Saturday

  25     11-10-2012    3       1           Sunday

  17     8-10-2012     2       1           sunday

  35     15-10-2012     4       1           Monday

  45     8-10-2012     3       1           Saturday

  50     9-10-2012     3       1           Sunday    

Final output after running code on tblA:

Workid |    Date     | ID | Multiplier | comments 

  12     10-11-2012    2       1           Sunday

  13     15-12-2012     4      1           Monday

  14     10-10-2012    3       1           Saturday

  25     11-10-2012    3       2           Sunday

  17     8-10-2012     2       1           sunday

  35     15-10-2012     4       1           Monday

  45     8-10-2012     3       1           Saturday

  50     9-10-2012     3       2           Sunday

As you can see from the table, the sundays that are preceded.

Answer 1

There's no need for VBA to do this; you might as well run a SQL query with a self-join and do the update directly.

This query would change the multiplier to 2 for all Sunday rows that have a record for the preceding Saturday with the same ID.

The syntax should be:

UPDATE tblA t1
INNER JOIN tblA t2 ON t1.ID = t2.ID AND t2.Date = DATEADD('d', -1, t1.date)
SET t1.Multiplier = 2
WHERE WEEKDAY(t1.date) = 1  -- assuming 1 is Sunday, might depend on regional setting

If you want to see what would happen before you do the update just change the update to a select * and remove the set statement.

Or make a backup of the table before you run it :)

Answer 2

Depending on how many records you are going to be accessing I think that this should work for you:

The first function is just to loop through all of the Sundays in the fwsat RecordSet. For each Sunday it will call the PrecedingSaturday function and will check to see if that ID has a Saturday that fell within one day of that Sunday.

Public Function CheckSunday()

    sqlFindSat = "Select WorkID, Date, Task, ID, Multiplier from tblA where  (weekday([date]) = 7) order by date, id, workId"
    sqlFindSun = "select WorkID, Date, Task,  ID, Multiplier from tblA where ( weekday([date]) = 1 ) order by date, id, workId"


    Set fwSun = db.OpenRecordset(sqlFindSun)
    Set fwsat = db.OpenRecordset(sqlFindSat)

    'For all Sundays
    Do While Not fwSun.EOF

        'If this Sunday has a Preceding Saturday. 
        If PrecedingSaturday(fwsat, fwSun!id, fwSun!Date) Then

            'Update multiplier to 2 for this Sunday
            fwSun.Edit
            fwSun!multiplier = 2
            fwSun.Update

        End If

        fwSun.MoveNext
    Loop

End Function

This function simply just accepts the RecordSet containing all of the Saturdays, and searches through them to see if any of them precedes the Sunday in question. If it does it returns true.

Public Function PrecedingSaturday(fwsat As Recordset, intID As Integer, dte As Date) As Boolean

    Dim blnPrecedingSaturday As Boolean

    blnPrecedingSaturday = False

    'For all Saturdays while a preceding Saturday hasn't already been found
    Do While Not fwsat.EOF And blnPrecedingSaturday = False

        'If Saturday's ID and Sunday's id match
        If fwsat!id = intID Then
            'If the Saturday and Sunday happened within 1 day
            If DateDiff("d", fwsat!Date, dte) = 1 Then
                'This is a preceding Saturday
                blnPrecedingSaturday = True
            End If
        End If

        fwsat.MoveNext
    Loop

    PrecedingSaturday= blnPrecedingSaturday

End Function

This is rough code since I don't have the table structure to properly test it. Though I think it should work for what you are looking for.