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

Click Here

Return from Multiple Criteria Update Query to Simply Access Home Page

or

Return to Microsoft Access Queries