Multiple Criteria Update Query
This tip Multiple Criteria Update Query is suitable for all versions of Microsoft Access.
Question:
I would like to create an Update Query to update multiple criteria into multiple updates based on the criteria.
For Example: If Field 1 contains "Yellow" or "Green" or "Red", can I update these records in Field 2 with "Caution" or "Go" or "Stop"?
Answer:
First of all, an Update Query updates your records and it cannot be reversed. So, whilst testing out the criteria, I would suggest you make a copy of your table so that you have a back up.
I am presuming the following:
Update Field 2 with 'Caution' if Field 1 is yellow;
'Go' if Field 1 is green and
'Stop' if Field 1 is red?
If this is the case:
In the Update To: row for Field2, type the following:
IIf([Field1]="Red","Stop",IIf([Field1]="Green","Go","Caution"))
The above is known as a Nested IIF statement for an overview then
Return from Multiple Criteria Update Query to Simply Access Home Page
or
Return to Microsoft Access Queries