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])));

Patton

Do you need it to be in one single UPDATE statement? I would just break it in three statements, it would be much easier to read and understand it later. I would do something like: UPDATE RBIA_Master SET RBIA_Master.RBI_Norm_Due = DateAdd ("m",2,[RBIA_Master]![DT_REPORT]) WHERE RBIA_Master.Size="small"; – Stillman

Thanks Pravin. I am using the method suggested as work around. It is working fine. I have sequenced multiple queries in a Macro. But still I would prefer to have a single update query. – Patton

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."} ));

Stillman