Query problem

[border20]

Hi, I have 4 fields in a query that can have a alphanumerical value, 0 or null

but when there is a value, they are the same for one ID :
example

0 0 1 0
2 2 2 0
0 0 3
3 0 3 0
8 0 0
0 0 0 0

I want to add a 5th field that would show the value if anyone of the fields has a value like so :

0 0 1 0 1
2 2 2 0 2
0 0 3 3
3 0 3 0 3
8 0 0 8
0 0 0 0

i tried this query code but it showed an error

field5: IIF([field1]<>0;[field1];IIF([field2]<>0;[field2];IIF([field3]<>0;[field3];IIF([field4]<>0;[field4];null)))

Can anyone tell me why ??

thx !!!

 

[pdx_man]

Use commas instead of semi-colons

 

[border20]

nope

I did that and it caused an immediate syntax error... its not it ... the semi-colon is what should be used.

Anyone else has ideas ?

 

[WayneRyan]

border20,

They should be commas.

You are missing a trailing parenthesis.

Wayne

 

[pdx_man]

Yup, you have 4 left parens and three right parens. The statement shouldn't have even been able to parse. Good catch Wayne! But, you still need commas.

 

[border20]

actually there are 4 right parens... one of them combined with a semicolon to do a smily

 

[pdx_man]

Ah, so right
So then,

1) Does the string parse when you click out of the row?
2) What is the error you are receiving?
3) Have you tried using commas?

 

[border20]

I have tried commas,, then a get a parse error... but with semicolons i dont get a parse error... but the error i get is that all the values of the new field are all #error

 

[WayneRyan]

Can you cut and paste the exact expression?

Wayne

 

[Mile-O]

If one of the parenthesis combined with a semi-colon about ten posts down, what was the semi-colon doing there after the Null statement?

Field5: IIf([Field1] <> 0, [Field1], IIf([Field2] <> 0, [Field2], IIf([Field3] <> 0, [Field3], IIf([Field4] <> 0, [Field4], Null))))

 

[pdx_man]

When you get the parse error, it should highlight where it thinks the problem is. Like Mile pointed out, I'm guessing it is with any syntax after the NULL statement. (Except for the parens, of course)

 

[border20]

here's the exact line i used

positions: IIF([Compression_Position]<>0;[Compression_Position];IIF([durabilité_position]<>0;[durabilité_position];IIF([MV-Absorption_position]<>0;[MV-Absorption_position];IIF([Entrée échantillonnage_Position]<>0;[Entrée échantillonnage_Position];null))))

if I change the semicolons to comas, then i get a parse error

 

[WayneRyan]

border20,

I pasted yours into a query, changing field names and got an error. Then I changed the semis to commas and it was OK.

Wayne

 

[pdx_man]

Yup, same too, here.

 

[border20]

strange... when i change to commas i get an error... says there's a misplaced comma sowhere or something

 

[raskew]

The switch() function might be a bit more readable, e.g.:

SELECT nz([fld1],0) AS a, nz([fld2],0) AS b, nz([fld3],0) AS c, nz([fld4],0) AS d, 
Switch(Val([a])>0,[a],Val([b])>0,[b],Val([c])>0,[c],Val([c])>0,[c],True,Null) AS z
FROM Table11;

returning...

<table>
a	b	c	d	z
0	0	1	0	1
2	2	2	0	2
0	0	3	0	3
3	0	3	0	3
8	0	0	0	8
0	0	0	0	
</table>

 

[pdx_man]

Hmmm ...
If you use Mile's format, it should/will work. Change them to commas and after you get the error, copy and paste the text here. I'd like to see the paste of the text that is giving you the error.

 

[border20]

Where do i put this code ??

<code>
SELECT nz([fld1],0) AS a, nz([fld2],0) AS b, nz([fld3],0) AS c, nz([fld4],0) AS d,
Switch(Val([a])>0,[a],Val()>0,,Val([c])>0,[c],Val([c])>0,[c],True,Null) AS z
FROM Table11;
</code>

I need nomething to but in a qeery field... and the fields (1 to 4) are already in the query

 

[pdx_man]

Hmmm ...
If you use Mile's format, it should/will work. Change them to commas and after you get the error, copy and paste the text here. I'd like to see the paste of the text that is giving you the error.

 

[border20]

positions: IIF([Compression_Position]<>0,[Compression_Position],IIF([durabilité_position]<>0,[durabilité_position],IIF([MV-Absorption_position]<>0,[MV-Absorption_position],IIF([Entrée échantillonnage_Position]<>0,[Entrée échantillonnage_Position],null))))

this is the exact line giving me an error ... it wont take the commas ( access 2000)
...

when I put in semicolons instead, it takes the syntax but displays #error in the query fields,,,