Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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
0
tomfarrar
Asked:
tomfarrar
  • 5
  • 4
1 Solution
 
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
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now