Answer

IIF statement returning a syntax error

Forum|Forum|3 months ago
August 21, 2025
7 replies
77 views

jlindsey
Freshman I

I am trying to make credit memo numbers return a negative amount and leave the all other doc types positive. This is the code I wrote and I cannot figure out how to get this to work correctly. Any help is appreciated.

=IIf([ARInvoice.DocType]='Credit Memo',(IIf([ARTaxTran.CuryTaxableAmt]<>Null Or [ARTaxTran.CuryTaxableAmt]<>0,-([ARInvoice.CuryDetailExtPriceTotal]-[ARTaxTran.CuryTaxableAmt]),0))),(IIf([ARTaxTran.CuryTaxableAmt]<>Null Or [ARTaxTran.CuryTaxableAmt]<>0,([ARInvoice.CuryDetailExtPriceTotal]-[ARTaxTran.CuryTaxableAmt]),0))

Best answer by DrewNisley

@jlindsey It is because of the DocType part of your IIf statement. It should actually be ‘CRM’ not ‘Credit Memo’. That is how it is saved in the database. Here’s the list of how they’re saved.

+15

darylbowman
Forum|Forum|3 months ago
August 21, 2025

Try this:

IIf(IsNull([ARTaxTran.CuryTaxableAmt],0)<>0,([ARInvoice.CuryDetailExtPriceTotal]-[ARTaxTran.CuryTaxableAmt])*IIf([ARInvoice.DocType]='Credit Memo',-1,1),0)

Daryl⚡Freelance Acumatica Developer (MVPx2🏆) for hire ⏩ linkedin.com/in/darylbowman

Like

J

jlindsey
Author
Freshman I
Forum|Forum|3 months ago
August 21, 2025

Try this:

IIf(IsNull([ARTaxTran.CuryTaxableAmt],0)<>0,([ARInvoice.CuryDetailExtPriceTotal]-[ARTaxTran.CuryTaxableAmt])*IIf([ARInvoice.DocType]='Credit Memo',-1,1),0)

This throws up a lot of errors. I’m not at my desk right now but I can add a picture of all the errors popping up.

Like

J

jlindsey
Author
Freshman I
Forum|Forum|3 months ago
August 21, 2025

So I figured out that I was missing some parentheses in one area and had too many in another. BUT the value will not show up as negative no matter how I format it. I have tried *-1, (-1), -, -(formula)…

=IIf([ARInvoice.DocType]='Credit Memo',(IIf([ARTaxTran.CuryTaxableAmt]<>Null Or [ARTaxTran.CuryTaxableAmt]<>0,-([ARInvoice.CuryDetailExtPriceTotal]-[ARTaxTran.CuryTaxableAmt]),0)),(IIf([ARTaxTran.CuryTaxableAmt]<>Null Or [ARTaxTran.CuryTaxableAmt]<>0,([ARInvoice.CuryDetailExtPriceTotal]-[ARTaxTran.CuryTaxableAmt]),0)))

Like

+3

DrewNisley
Pro I
Answer
Forum|Forum|3 months ago
August 22, 2025

@jlindsey It is because of the DocType part of your IIf statement. It should actually be ‘CRM’ not ‘Credit Memo’. That is how it is saved in the database. Here’s the list of how they’re saved.