Conditional Update

[colmtourque]

I am trying to use an IIF statement in an update query. To say that if the months is on a quarter (Jan, April, July,Sept or Dec) then input a value, but if not then do nothing.

I tried to start simple with Just January and did this
UPDATE [Acct: Fixed Charges] INNER JOIN [xAcct: Fixed Charges non-Standard] ON [Acct: Fixed Charges].IDNumber = [xAcct: Fixed Charges non-Standard].[ID Number]
SET iif(format(DATE(),"mm")=1,[Acct: Fixed Charges].AgentDues = [xAcct: Fixed Charges non-Standard].[Agent Dues]);

I get a Syntax Error in Update when I try to run.

Can you not do a IIF in an update query?

Thanks in advance for the help.

 

[RV]

The syntaxis of your IIF function is incorrect.

This is the basic syntax for the IIF function:

IIf(expr, truepart, falsepart)

Change your IIF function to this:

SET [Acct: Fixed Charges].AgentDues = IIF(format(DATE(),"mm")=1,
[xAcct: Fixed Charges non-Standard].[Agent Dues],[Acct: Fixed Charges].AgentDues);

Better is to use this UPDATE statement:

UPDATE....
SET [Acct: Fixed Charges].AgentDues =
[xAcct: Fixed Charges non-Standard].[Agent Dues]
WHERE format(DATE(),"mm") in (1,4,7,10)


DON'T use spaces in tablenames or column names.

P.S there are only four quarters in a year...

RV

 

[colmtourque]

Thanks a bunch

I use Martian counting techniques, hince the fifth Quarter