We help IT Professionals succeed at work.

# IIf syntax question

on
What is wrong with...

=IIf([txtTotalPartNdemand]-([txtWIPqtyAM]+[txtFGqty]+[txtTotWHqty])<0,0,[txtTotalPartNdemand]-([txtWIPqtyAM]+[txtFGqty]+[txtTotWHqty])

--Steve
Comment
Watch Question

## View Solution Only

Database Architect / Application Developer
Top Expert 2007

Commented:
missing paren at end

= IIf([txtTotalPartNdemand] - ([txtWIPqtyAM] + [txtFGqty] + [txtTotWHqty]) < 0, 0, [txtTotalPartNdemand] - ([txtWIPqtyAM] + [txtFGqty] + [txtTotWHqty]))
Top Expert 2016
Commented:
use the nz() function to handle null values

=IIf(nz([txtTotalPartNdemand])-(nz([txtWIPqtyAM])+nz([txtFGqty])+nz([txtTotWHqty]))<0,0,nz([txtTotalPartNdemand])-(nz([txtWIPqtyAM])+nz([txtFGqty])+nz([txtTotWHqty])))
MIS Liason
Most Valuable Expert 2012

Commented:
...or perhaps you could first explain what happens when you run it...?
Database Architect / Application Developer
Top Expert 2007

Commented:
Steve ... the IIF did not compile until I added a trailing right Paren ...

mx
Retired IT Professional

Commented:
IIF Syntax helper:

IIF(True, TruePart, FalsePart)

Having another IIF for TruePart, copy the whole and replace TruPart.

IIF(True, IIF(True, TruePart, FalsePart), FalsePart)
IIF(x=2, IIF(y=3, 10, 20), 3)

Having another IIF for FalsePart, copy the whole and replace FalsePart.

IIF(True, TruePart, IIF(True, TruePart, FalsePart))
IIF(x=2, 10, IIF(y=3, 10, 20))

Having both TruePart and FalsePart IIFs

IIF(True, IIF(True, TruePart, FalsePart), IIF(True, TruePart, FalsePart))