Need assistance with Query Expression that pulls values from lowest populated tier level

[skydivetom]

Hello:



I need assistance with developing a query that checks up to 5 fields and only outputs a value from the lowest tier level (T5 up to T1).

Background:
- I have a table that contains 5 fields: [TIER1] through [TIER5].
- Not all of the 5-tiered fields are populated. That is, sometimes I may have a value in tier 1 (aka T1), no value in T2, but then a value in T3.
- Going form the *lowest* (T5) to the *highest* (T1) tier level -- aka right to left -- I want my expression to pick up the value on the *lowest* level (starting with T5).
- In this case, I have manually highlighted (in yellow) the values that I want the expression to pick up.

Example #1 (where ID = 1):
- T1 = 113; T2 and T3 are null, T4 = 498, T5 = null.
- Business rule for expression: Given T5 = null, I check T4. In this case, T4 <> null. Hence, I output "498".

Example #2 (where ID = 9):
- Business rule for expression: T5 = null, T4 = null, T3 = 312. Hence, I output "312".

I added the following expression to my query.

Expr: IIf(IsNull([TIER5]),[TIER4],IIf(IsNull([TIER4]),[TIER3],IIf(IsNull([TIER3]),[TIER2],IIf(IsNull([TIER2]),[TIER1],[TIER5]))))

Unfortunately, as illustrated in the JPG, the results are incorrect. With the exception of 4 (out of 15), I get only null values.
And for those 4 records (ID 1, 5, 6, 476) where I do display values, record # 6 is incorrect. It shows "165" when it should have been "562".

My question: How do I need to rewrite the expression so that I get the output that is displayed in JPG (cell range G2:G16)?

Thank you,
EEH

[June7]

SELECT *, Nz(Tier5, Nz(Tier4, Nz(Tier3, Nz(Tier2, Tier1)))) AS T FROM table;

[skydivetom]

June7... thank you!! That's the perfect solution. I appreciate your assistance.