Access IIf statement giveing me the wrong result.

What is wrong with the following IIf statement?  I am getting negative values in the AdjustedInventoryValue field when I want it to be zero.  Sample data below the formula.  Thanks.

IIf(IsNull([InventoryReserve]![UnpostedINVAmount]),[InventoryReserve]![Value_USD],IIf([InventoryReserve]![Value_USD]+[InventoryReserve]![UnpostedINVAmount]<0,0,[InventoryReserve]![Value_USD]+[InventoryReserve]![UnpostedINVAmount]))

Value_USD      UnpostedInvAmount      AdjustedInventoryValue
 100                                                                     100
 50                              -75                                             0
150                               25                                         175
-25                              10                                           0
LVL 7
tomfarrarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
The first part of the IIf() returns Value_USD when UnpostedInvAmount is null.  This expression might work better for you:
IIf(Nz([InventoryReserve]![UnpostedINVAmount],0) + Nz([InventoryReserve]![Value_USD], 0) < 0, 0, (Nz([InventoryReserve]![UnpostedINVAmount],0) + Nz([InventoryReserve]![Value_USD], 0))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomfarrarAuthor Commented:
Maybe that could work.  What does the Nz() function do?  Thanks.
0
PatHartmanCommented:
Nz is an abbreviation for Null to zero but the Nz() function lets you specify the actual substitution.

Nz(somefield, 934) returns 934 if somefield is null
Nz(somefield, 0) returns 0 if somefield is null
Nz(somefield, "xyz") returns "xyz" if somefield is null
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

tomfarrarAuthor Commented:
Okay, I am trying that out.  The formula as written did not work because of a matching "()" problem.  It appears to me the "(" after 0,0, does not need to be there.  Do you agree?  Thanks.
0
PatHartmanCommented:
Correct.
0
tomfarrarAuthor Commented:
Thanks, Pat, you formula worked perfectly!  I will remember Nz().....
0
tomfarrarAuthor Commented:
Thanks again....
0
Nick67Commented:
Null happens, man.
And when it does, you use Nz() to deal with it
I will remember Nz().....

I sent my niece a t-shirt with that on the front
On the back was
Nz(SomethingTrivial, "Oh, well")
Nz(SomethingSerious,"Crap!")
Nz(SomethingCatastrophic,"Run! Run for the hills!")

Her IT guys laugh every time she wears it :)
0
PatHartmanCommented:
My husband played softball on an intercompany league and each department had their own t-shirts.  Accounting was green with a dollar sign.  Personnel was yellow with happy faces.  IT was blue with a beer stein and the words "work is the curse of the drinking class" printed in hexadecimal.
0
tomfarrarAuthor Commented:
That's funny stuff....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.