Round Function in Update Query Returns Syntax Error

[mbiagiot]

Hi all, new member and first time post.



I'm currently creating a process and I'm having the strangest issue that I can't seem to explain. Essentially what I'm trying to do is update values on a table to round based on specific criteria (Monthly vs Yearly).

Here is the SQL code that works when I run this update query:

UPDATE TB_1201_SfaxCM 
   SET TB_1201_SfaxCM.MRR = IIf([TB_1201_SfaxCM].[Term]="Monthly",([TB_1201_SfaxCM].[DIDS]-1)*5+[TB_1201_SfaxCM].[Plan Value],([TB_1201_SfaxCM].[DIDS]-1)*4+[TB_1201_SfaxCM].[Plan Value]/12);

However, I want yearly to round to 2 decimal places. I've tried this and keep getting a syntax error:

UPDATE TB_1201_SfaxCM 
   SET TB_1201_SfaxCM.MRR = IIf([TB_1201_SfaxCM].[Term]="Monthly",([TB_1201_SfaxCM].[DIDS]-1)*5+[TB_1201_SfaxCM].[Plan Value],([TB_1201_SfaxCM].[DIDS]-1)*4+ROUND([TB_1201_SfaxCM].[Plan Value]/12,2);

Thanks!

[pbaldy]

You don't have the closing parentheses for the Round() function.

[June7]

Why save calculated value?

Missing closing paren. Always make sure [], (), ", ' are in pairs (an even number total count).

UPDATE TB_1201_SfaxCM
SET TB_1201_SfaxCM.MRR = IIf([TB_1201_SfaxCM].[Term]="Monthly", ([TB_1201_SfaxCM].[DIDS]-1)*5+[TB_1201_SfaxCM].[Plan Value],([TB_1201_SfaxCM].[DIDS]-1)*4+ROUND([TB_1201_SfaxCM].[Plan Value]/12,2));

[mbiagiot]

Can't believe I missed that. However, I'm still somehow getting an error.

I pasted in your code over the old one, and after trying to run the query i'm still getting: "Compile error. in query expression"

Not sure what's going on

Why save calculated value?

Missing closing paren. Always make sure [], (), ", ' are in pairs (an even number total count).

UPDATE TB_1201_SfaxCM
SET TB_1201_SfaxCM.MRR = IIf([TB_1201_SfaxCM].[Term]="Monthly", ([TB_1201_SfaxCM].[DIDS]-1)*5+[TB_1201_SfaxCM].[Plan Value],([TB_1201_SfaxCM].[DIDS]-1)*4+ROUND([TB_1201_SfaxCM].[Plan Value]/12,2));

[June7]

What data type is MRR?

I don't see anything else wrong with expression.

Still wonder why you are saving a calculation that can be done when needed.

Does the expression work in a SELECT query?

[orange]

I think you need to move the Round to include the entire "false" side of the iif.

UPDATE TB_1201_SfaxCM
SET TB_1201_SfaxCM.MRR = IIf([TB_1201_SfaxCM].[Term] = "Monthly", ([TB_1201_SfaxCM].[DIDS] - 1) * 5 + [TB_1201_SfaxCM].[Plan Value], ROUND(([TB_1201_SfaxCM].[DIDS] - 1) * 4 + ([TB_1201_SfaxCM].[Plan Value] / 12), 2));

[June7]

Should be able ro round one term of calc. Works for me.

[orange]

June,

My comment wasn't whether it would work, I was just trying to ensure the Yearly/false side was rounded to 2 digits.
It may do the rounding even if the terms have different number of digits.. I'm not sure, but putting all terms within the round should ensure 2 digits.

[June7]

Okay, agree but don't expect that will fix error although could be wrong.

[orange]

Perhaps, OP could try this

UPDATE TB_1201_SfaxCM
SET MRR = ROUND(IIf([Term] = "Monthly", ([DIDS] - 1) * 5 + [Plan Value], ([DIDS] - 1) * 4 + ([Plan Value] / 12)), 2);

[mbiagiot]

Hi All, I'll try this today at work and hopefully get the results I'm looking for so I can move on with creating this process. Such a pain!

Thanks for the help here everyone

Perhaps, OP could try this

UPDATE TB_1201_SfaxCM
SET MRR = ROUND(IIf([Term] = "Monthly", ([DIDS] - 1) * 5 + [Plan Value], ([DIDS] - 1) * 4 + ([Plan Value] / 12)), 2);