Solved

Access IIf statement giveing me the wrong result.

Posted on 2014-12-03
10
180 Views
Last Modified: 2014-12-03
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
Comment
Question by:tomfarrar
  • 5
  • 4
10 Comments
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40478711
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40478731
Maybe that could work.  What does the Nz() function do?  Thanks.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40478761
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 7

Author Comment

by:tomfarrar
ID: 40478769
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 40478778
Correct.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40478821
Thanks, Pat, you formula worked perfectly!  I will remember Nz().....
0
 
LVL 7

Author Closing Comment

by:tomfarrar
ID: 40478823
Thanks again....
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40478851
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 40479094
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40479170
That's funny stuff....
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question