Update Table in Access

Question

In MS-Access 2007, I have a table, [Test_Master] where I have a field [DT_REPORT]. I want to update [Test_Norm_Due] by 2 months if field [Size] = "small". If the field "Size" = "Med." then by 3 months. I create below query but it is throwing Syntax error. Can someone help.

UPDATE Test_Master 
SET Test_Master.Test_Norm_Due = 
    IIF((([Test_Master]![Size]="small")), DateAdd(("m",2,[Test_Master]![DT_REPORT]))), 
         IIF((([Test_Master]![Size]="med.")), DateAdd(("m",3,[Test_Master]![DT_REPORT])));

Answer 1

I believe you have a problem with your parentheses - try nesting them using an external Text editor (like notepad++) for greater visibility - also, you are using extra parentheses that are getting in your way, try simplifying; and you're missing one final condition - what should happen with Test_Norm_Due when Size is neither "small" nor "med."

Note that syntax for IIF is:

IIF (condition, value if true, value if false). 

You are nesting IIFs, so you should have something like:

IIF (condition, value if true, IIF(other condition, value if true, value if false))

Try something like this (I broke it in multiple lines just to try to make it more visible for you).

UPDATE Test_Master SET Test_Master.Test_Norm_Due = 
IIF (([Test_Master]![Size]="small"), 
       DateAdd("m",2,[Test_Master]![DT_REPORT]), 
        IIF (([Test_Master]![Size]="med."), 
               DateAdd("m",3,[Test_Master]![DT_REPORT]), 
               {missing value - What happens if it's neither "small" nor "med."} ));