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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.