access 2007 update query using CASE

[lazyme]

Hi,

I tried writing an sql update query for Access 2007 using a case statement. This is the format

UPDATE titles
       SET price =
                 CASE
                   WHEN (price < 5.0 AND ytd_sales > 999.99)
                                   THEN price * 1.25
                   WHEN (price < 5.0 AND ytd_sales < 1000.00)
                                   THEN price * 1.15
                   WHEN (price > 4.99 AND ytd_sales > 999.99)
                                   THEN price * 1.2
                   ELSE price
        END

But I found that access does not support such queries. What would be an alternate way to write the query in access to achieve what I want.

Thanks

[vladK]

Use iff

[VBA]
UPDATE titles
SET price = IIF (price < 5.0 AND ytd_sales > 999.99, price * 1.25, IIF(price < 5.0 AND ytd_sales < 1000.00, price * 1.15, IIF(price > 4.99 AND ytd_sales > 999.99, price * 1.2, price)))


[/VBA]

[lazyme]

Ok will try this.
Thanks