We help IT Professionals succeed at work.

IIf syntax question

SteveL13
SteveL13 asked
on
What is wrong with...

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

--Steve
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)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])))
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
...or perhaps you could first explain what happens when you run it...?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

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

mx
Hamed NasrRetired 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)
Fill your real values: ex:
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))
Fill your real values: ex:
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))
Fill your real values: ex:
IIF(x=2, IIF(y=3, 10, 20), IIF(y=3, 10, 20))

You can expand by (nesting IIFs) replacing TruePart/FalsePart as presented.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
SteveL13
The first problem with your statement was the missing paren ...

mx