Solved

IIF statement returning a syntax error

1 year ago
Aug 21, 2025 2:46 PM UTC
7 replies
121 views
Translate All

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.

View original

+17

darylbowman
1 year ago
Aug 21, 2025 2:59 PM UTC

Try this:

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

Translate

Daryl 🧩 I build Acumatica add-ons 🔗deftstrokesdev.com/products?ref=forum_sig

Like

J

jlindsey
Author
Freshman I
1 year ago
Aug 21, 2025 3:44 PM UTC

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.

Translate

Like

J

jlindsey
Author
Freshman I
1 year ago
Aug 21, 2025 8:40 PM UTC

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

Translate

Like

+4

DrewNisley
Pro I
Answer
1 year ago
Aug 22, 2025 11:47 AM UTC

@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.