Combining like update queries

[xcr800man]

I am looking to take 4 existing update queries and transform them into one. Here are the queries.

UPDATE [Employee Information] SET [Employee Information].SickDaysAvailable = 0, [Employee Information].VacationDaysAvailable = 5, [Employee Information].BerevementDaysAvalable = 2, [Employee Information].PersonalDaysAvailable = 1, [Employee Information].[Excused U/P Days Off] = 0, [Employee Information].[Unexcused U/P Days Off] = 0, [Employee Information].Tardies = 0
WHERE ((([Employee Information].AnniversaryDate)>=Date()-1825 And ([Employee Information].AnniversaryDate)<=Date()-365));



UPDATE [Employee Information] SET [Employee Information].SickDaysAvailable = 0, [Employee Information].VacationDaysAvailable = 10, [Employee Information].BerevementDaysAvalable = 2, [Employee Information].PersonalDaysAvailable = 3, [Employee Information].[Excused U/P Days Off] = 0, [Employee Information].[Unexcused U/P Days Off] = 0, [Employee Information].Tardies = 0
WHERE ((([Employee Information].AnniversaryDate)>=Date()-3650 And ([Employee Information].AnniversaryDate)<=Date()-1825));



UPDATE [Employee Information] SET [Employee Information].SickDaysAvailable = 0, [Employee Information].VacationDaysAvailable = 15, [Employee Information].BerevementDaysAvalable = 2, [Employee Information].PersonalDaysAvailable = 3, [Employee Information].[Excused U/P Days Off] = 0, [Employee Information].[Unexcused U/P Days Off] = 0, [Employee Information].Tardies = 0
WHERE ((([Employee Information].AnniversaryDate)>=Date()-7300 And ([Employee Information].AnniversaryDate)<=Date()-3651));



UPDATE [Employee Information] SET [Employee Information].SickDaysAvailable = 0, [Employee Information].VacationDaysAvailable = 20, [Employee Information].BerevementDaysAvalable = 2, [Employee Information].PersonalDaysAvailable = 3, [Employee Information].[Excused U/P Days Off] = 0, [Employee Information].[Unexcused U/P Days Off] = 0, [Employee Information].Tardies = 0
WHERE ((([Employee Information].AnniversaryDate)<=Date()-7300));



I am hoping that someone can help me out.

[narayanis]

access syntax is trickier than SQL, but i think this will help get you there:

UPDATE [Employee Information] 
SET [Employee Information].VacationDaysAvailable =
iif ((([Employee Information].AnniversaryDate)>=Date()-1825 And ([Employee Information].AnniversaryDate)<=Date()-365)), 5, 
    iif ((([Employee Information].AnniversaryDate)>=Date()-3650 And ([Employee Information].AnniversaryDate)<=Date()-1825)), 10,
        iif ((([Employee Information].AnniversaryDate)>=Date()-7300 And ([Employee Information].AnniversaryDate)<=Date()-3651)), 15,
            iif ((([Employee Information].AnniversaryDate)<=Date()-7300)), 20,0))))

[xcr800man]

Thanks! I will try that