SWITCH statement

I have the following table named [SalesByCity]:

City;SalesAmount
---------------------------
Los Angeles;1000
New York;1200
Boston;700

If I were using T-SQL, I could use the CASE statement, but I need to do this in Access (which does not support the CASE statement). However, Access SQL does support the SWITCH statement. Is there a way to use a SWITCH statement in Access to get a query that assigns 1 million to all cities except for Boston (in which case the dollar amount is 500000)? I am also trying to avoid using the IIF statement.

The result would be something like…

City;Valuation
---------------------------
Los Angeles;1000000
New York;1000000
Boston;500000

I thought that my query might go something like this, but I get an error message:

Please Login or Register  to view this content.

Thanks.

Get rid of [salesamount]=, having that will mean the SWITCH will only return True/False.

By the way, why not use Iif?

That would probably be simpler.

Formula:

Please Login or Register  to view this content.

SWITCH returns a value, it won't carry out an operation on a field.

To use SWITCH in this example return the multiplier.

Formula:

Please Login or Register  to view this content.