Looping through a tablej's record and change data based on various field values

I have a current salary table that each quarter is archived and the most recent salary for the previous quarter is the starting salary for the current quarter.  However, there could be up to three salary increases in a quarter, so I need the process to check each employees record and if there is a value in Sal3, that becomes Sal1.  If sal3 is 0, then it needs to check Sal2 and if that has a value, Sal2 becomes Sal1 for the new quarter.  If Sal2 is 0, then Sal1 for the previous quarter is still Sal1 for the new quarter.  I have the attached query, but I realized I need to loop thorugh each record to evalue the salary fields, update Sal1 to the correct amount and then set Sal2 and Sal3 to 0.  Dates values work, it is only the salary values that do not update correctly.
'Sets the workdays in the TblEmployeeHRData table to the full number of workdays calculated by
'the workdays function, anyone whose start date was greater than the beginning of the previous
'incentive cycle quarter by default worked the full number of days in the quarter.
strUpdate = "UPDATE TblEmployeeHRData SET TblEmployeeHRData.HRSal1Date = #" & gdteThisQrtDate & "#, " & _
    "TblEmployeeHRData.HRSalary1 = IIf([HRSalary3]=0,IIf([HRSalary2]=0,[HRSalary1],[HRSalary2]),[HRSalary3]), " & _
    "HRSalary2 = 0, HRSalary3 = 0, HRSal2Date = Null, HRSal3Date = Null, " & _
    "HRDaysPaid1 = " & intDays & ", HRDaysPaid2 = 0, HRDaysPaid3 = 0 " & _
    "WHERE TblEmployeeHRData.HRStartDate <#" & gdteLastQtrDate & "# "
        DoCmd.RunSQL strUpdate

Open in new window

Sandra SmithRetiredAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
try this

strUpdate = "UPDATE TblEmployeeHRData SET TblEmployeeHRData.HRSal1Date = #" & gdteThisQrtDate & "#, " & _
    "TblEmployeeHRData.HRSalary1 = IIf([HRSalary3]>0,[HRSalary3],IIF([HRSalary2]>0,[HRSalary2],[HRSalary1])), " & _
    "HRSalary2 = 0, HRSalary3 = 0, HRSal2Date = Null, HRSal3Date = Null, " & _
    "HRDaysPaid1 = " & intDays & ", HRDaysPaid2 = 0, HRDaysPaid3 = 0 " & _
    "WHERE TblEmployeeHRData.HRStartDate <#" & gdteLastQtrDate & "# "
0
 
Sandra SmithRetiredAuthor Commented:
Thank you.  I could not see where my logic was off.

Sandra
0
 
Rey Obrero (Capricorn1)Commented:
compare them
yours

= IIf([HRSalary3]=0,IIf([HRSalary2]=0,[HRSalary1],[HRSalary2]),[HRSalary3])

mine

= IIf([HRSalary3]>0,[HRSalary3],IIF([HRSalary2]>0,[HRSalary2],[HRSalary1]))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.