Nested IIf statement with IsNull

[halnurse]

Hello all - I am having difficulty getting my query to do what I want. I have a table with an item description and then the quantity needed however the quantity could be in one of three columns in the table. I want the query to look at the columns and if the first (Loc1) is null then use the second column (Loc2). If the second column is null, look at the third column (Loc3). I am only having problems if the first two columns are empty and it needs to move to the third column. The query shows a null value here. Can someone point out what I am doing wrong please! Thanks for your time!

Here is the code I have written:



Quantity: IIf(Not IsNull([tblTSP_Material_Import]![Loc1]),[tblTSP_Material_Import].[Loc1],IIf(IsNull([tblTSP_Material_Import]![Loc1]),[tblTSP_Material_Import]![Loc2],IIf(IsNull([tblTSP_Material_Import].[Loc1]) And IsNull([tblTSP_Material_Import]![Loc2]),[tblTSP_Material_Import]![Loc3],"")))

[June7]

Quantity: IIf(Not IsNull([Loc1]), [Loc1], IIf(Not IsNull([Loc2]), [Loc2], [Loc3]))

Or:

Quantity: Nz(Loc1, Nz(Loc2, Loc3))

Nz and IsNull are both VBA function calls. For strictly SQL use:

Quantity: IIf(Not [Loc1] Is Null, [Loc1], IIf(Not [Loc2] Is Null, [Loc2], [Loc3]))

[halnurse]

Thank you, that worked!