Query problem... help plz...

[border20]

I have a query with 4 fields that can either be null, 0, or a text value like the folowing example:

f1 f2 f3 f4
0 12 0 0
0 0 23 0
3 3 0 0
0 0 65 0

What I want is to be able to have a fifth column with the text value of the row.... example

f1 f2 f3 f4 pos
0 12 0 0 12
0 0 23 0 23
3 3 0 0 3
0 0 65 0 65

I put the folowing code in my query but instead of getting the numerical values I want in the fifth column... I only get #error in the fifth column...

pos : IIF([f1]<>0;[f1];IIF([f2]<>0;[f2];IIF([f3]<>0;[f3];IIF([f4]<>0;[f4];null))))

Someone already suggested replacing the ";"'s with comas but that only does a syntax error while the code I already have doesn't create syntaxe errors,,,

Can anyone help ?

 

[FoFa]

pos : IIF([f1]<>0,[f1],NULL) & IIF([f2]<>0,[f2],NULL) & IIF([f3]<>0,[f3],NULL) & IIF([f4]<>0,[f4],null)

Your problem is each is a seperate check, then concatenated together. You can replace NULL with a value if you want, but basically you need if this aint zero, than use this, else use this.
IIF(expresion,true,false)

 

[border20]

thanks that seems to work