Multi IIF statement 2

[yehong]

I need to write an IIF statement for following code:

(
If Instr('abcdQ1','Q1')>0 then (select q1 from tableA),
If instr ('abcdQ2','Q2')>0 then (Select q2 from tableA)
Else (select q5 from tableA)
end if
) as Q

Is it possible to wrtie multip IIF statements in Access 2003?
thanks.

 

[PHV]

IIf([field name 1] Like '%Q1%', [q1],
IIf([field name 2] Like '%Q2%', [q2], [q5])) AS Q

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

hi yehong,

Since your example uses literals in the Instr function, it is ALWAYS True.

What FIELD do you want to examine instead? Like this???

Select....
iif(Instr([WhatField],'Q1')>0,A.q1,iif(Instr([WhatField],'Q2')>0,A.q2,A.q5)) as 'Q' .....
From TableA A

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[PHV]

OOps, you may have to replace all % by *
Will one day be JetSQL more ANSI compliant ...

 

[yehong]

The 'WhatField' string is like:
'ABCD_Q1'
'ABCD_Q2'
'ABCD_Q3'
'ABCD_Q4'
'ABCD_Q5'...
This string is the value of a cobo box on a form. So when a user selects this value, in the background my IIF query will kick in.

 

[PHV]

Like this ?
SELECT ...
IIf([Forms]![name of mainform]![name of combo] Like '*Q1',[q1],
IIf([Forms]![name of mainform]![name of combo] Like '*Q2',[q2],
IIf([Forms]![name of mainform]![name of combo] Like '*Q3',[q3],
IIf([Forms]![name of mainform]![name of combo] Like '*Q4',[q4],
[q5])))) AS Q
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886