title: Iterate over a table and update matching recordset - Access - Vba
body: <h2>Issue</h2>
  
  <p>I built a tracking system with unique <code>workID</code> and <code>Id</code>s 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. </p>
  
  <p>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. </p>
  
  <h2>Code</h2>
  
  <pre><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: " &amp; fwSat!Date &amp; " Curr Sun: " &amp; 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
  </code></pre>
  
  <p>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. </p>
  
  <p>Note: the sample below isn't real sample. The comment section is to show what day of the week it is. </p>
  
  <h2>Sample table tblA</h2>
  
  <pre><code>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    
  </code></pre>
  
  <h2>Final output after running code on tblA:</h2>
  
  <pre><code>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
  </code></pre>
  
  <p>As you can see from the table, the sundays that are preceded.</p>