Solved

Access IIf statement giveing me the wrong result.

Posted on 2014-12-03
10
177 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 34

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 34

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
 
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 34

Expert Comment

by:PatHartman
ID: 40478778
Correct.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now