Robert Mazurowski Robert Mazurowski - 23 days ago 10
SQL Question

MS Access SQL IIF if false then return old value

I have a question I am doing an Update query and using the IIF function in the process:

Update someTable as tab Set tab.[field1] = "new filed1 value", tab.[field2] = IIF
(
tab[field2] = "some value" , "New value",IIF
(
tab.[field2] = tab.[field2],tab.[field2]))

)) Where tab.[field1] = "Old field 1 value"


With the above query I wanto to change field2 to "New Value" if it equals to "some value" but if it doesn't then I don't want to change it (or put the same value as it was like the above)

So my question is would the below line in the above query work as expected?

IIF
(
tab.[field2] = tab.[field2],tab.[field2]
)

Answer

I think the expression is too complicated. This is what you want:

Update someTable as tab
    Set tab.[field1] = "new filed1 value",
        tab.[field2] = IIF(tab.[field2] = "some value", tab.[field2])
    Where tab.[field1] = "Old field 1 value";