MS Access: Need to add another condition to IIF statement

Hello,
I'm trying to return the value of one column based on the condition of other columns.  If Value2 = 0, then return Value1.  But, if Value1 = 0, then return Value3. Can you please review my IIf statement and tell what I need to add for completion?  

For example, in the attached database (Record 1), should return 900 since Value1  and Value2 = 0, but there is an amount in Value3.
Database-Price2.mdb
Wonderwall99Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
another version

SELECT [Item Table].[Item#], [Item Table].Value1, [Item Table].Value2, [Item Table].Value3, IIf([Value2]=0 And [Value1]=0,[Value3],IIf([Value2]=0,[Value1],[Value2])) AS Expr1
 FROM [Item Table];
0
 
Rey Obrero (Capricorn1)Commented:
test this

SELECT [Item Table].[Item#], [Item Table].Value1, [Item Table].Value2, [Item Table].Value3, IIf([Value2]=0 And [Value1]=0,[Value3],IIf([Value1]>0,[Value1],[Value2])) AS Expr1
FROM [Item Table];
0
 
Rey Obrero (Capricorn1)Commented:
or maybe this

SELECT [Item Table].[Item#], [Item Table].Value1, [Item Table].Value2, [Item Table].Value3, IIf([Value2]=0 And [Value1]=0,[Value3],IIf([Value2]>0,[Value2],[Value1])) AS Expr1
FROM [Item Table];
0
 
Dale FyeCommented:
Personally, I like to put this type of convoluted logic in a function, which will allow me to document, via remark statements, my business logic.  This is much easier to maintain in the long run.
0
 
Gustav BrockCIOCommented:
This one exactly mimics your English definition:

SELECT
    [Item Table].[Item#],
    [Item Table].Value1,
    [Item Table].Value2,
    [Item Table].Value3,
    IIf([Value1]=0,IIf([Value2]=0,[Value3],[Value2]),[Value1]) AS ValueX
FROM
    [Item Table];

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.