5 if clauses

[niklassbordone]

All,



I have browsed this forum for quite a while now but I couldn't find a similar issue like the one that I am facing. So far this type of expression has never created a problem but right now I am a little bit lost and hope someone can help me:

I have 5 different columns:
- Test1
- Test2
- Test3
- Test4
- Test5

The first 4 columns (Test 1 - Test 4) are sometimes blank (""). Test 5 always has a data record that is not null.

I want to merge those columns into one based on the following rule:
If Test 1 is null => check if Test 2 is null => if yes, check if Test 3 is null => if yes, check if Test4 is null => if yes, enter Test 5, else Test4, then Test3, then Test2 and Test 1

I used a simple if code
IIf([TEST_1]="",IIf([TEST_2]="",IIf([TEST_3]="",IIf([TEST_4]="",[TEST_5],[TEST_4]),[TEST_3]),[TEST_2]),[TEST_1])

But the result is that Test 1 - 4 are consolidated correctly. But as soon as Test 1-4 are null I get to see a blank instead of "Test 5".

I hope I was able to explain it properly.

Any clue what my mistake is?

Thanks
Niklas

[Beetle]

Try the Switch function instead

SWITCH([TEST_1]<>"",[TEST_1],[TEST_2]<>"",[TEST_2],[TEST_3]<>"",[TEST_3],[TEST_4]<>"",[TEST_4],[TEST_5]<>"",[TEST_5])

Also, trapping for "" is not the same as trapping for Null. If any of these fields may actually be Null, then you'll want to incorporate the Nz function as well;

SWITCH(Nz([TEST_1],"")<>"",[TEST_1],............

[CJ_London]

assuming they are nulls, you could just use a nested nz function

nz(test1,nz(test2,nz(test3,nz(test4,test5))))