Nesting UPDATEs/WHEREs in Access

[sqleptical]

I'm trying to update fields based on 1 criteria, and update other fields based on another criteria all in the same query using MS Access 2000. Any Ideas?

 

[tlbroadbent]

If you gave an example, I might have an idea. There may be limited things you can do depending on how you define the criteria and the nature of the updates. But it is not straightforward and simple. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[sqleptical]

Thank you for your response. After a couple of hours of trying unsuccesful queries and searching the web for the answer, I gave up and put two seperate UPDATE queries into a macro (as seen below) and it works fine, but if you know a better way, it would really put my mind to ease. It seems to me I should be able to replace the 1st semicolon with an AND and it should work in 1 query, but it doesn't.

UPDATE table1 SET columnn1 = "T1"
WHERE column2 = "D1";

UPDATE table1 SET column1 = "T2"
WHERE column2 = "D2";

Thanks again.

David Hovey

 

[tlbroadbent]

There is a way to do this update in one pass.

UPDATE table1
SET columnn1 = IIF(Column2="D1","T1","T2&quot
WHERE column2 In ("D1", "D2"
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.