Help with IIf Statement please

[theborgking1]

Hi all,

I hope you are all well.

Could someone please kindly help me with the following....

I am trying to get an IIf statement to work, but I am not quite sure how I can lay it out properly so that it works

So this is what I need

(IIF(([PAON_TEXT]= "Abbey Parade" AND [SAON_TEXT] AND [SAON_START_NUM]), (THATS THE CONDITION)

[SAON_START_NUM] & " ", (THATS THE TRUE PART)

(IIf(Not IsNull([SAON_TEXT]),[SAON_TEXT] & " ","") & (IIf(Not IsNull([SAON_START_NUM]),[SAON_START_NUM],"") & IIf(Not IsNull([SAON_START_SUFFIX]),[SAON_START_SUFFIX] & " ") & IIf(Not IsNull([SAON_END_NUM])," - " & [SAON_END_NUM] & " ","") & IIf(Not IsNull([SAON_END_SUFFIX]),[SAON_END_SUFFIX] & " ","") & IIF(([PAON_TEXT]= "Abbey Parade" AND [SAON_TEXT] AND [SAON_START_NUM])," ","") & iif(NOT IsNull([PAON_START_NUM])," " & [PAON_TEXT],"")
(ALL OF THIS THE FALSE PART)

I know the syntax for the FALSE PART is wrong how can I make the whole statement to work?

Please please help!

:banghead:

 

[CJ_London]

(IIF(([PAON_TEXT]= "Abbey Parade" AND [SAON_TEXT] AND [SAON_START_NUM]), (THATS THE CONDITION)

[SAON_START_NUM] & " ", (THATS THE TRUE PART)

(IIf(Not IsNull([SAON_TEXT]),[SAON_TEXT] & " ","") & (IIf(Not IsNull([SAON_START_NUM]),[SAON_START_NUM],"") & IIf(Not IsNull([SAON_START_SUFFIX]),[SAON_START_SUFFIX] & " ") & IIf(Not IsNull([SAON_END_NUM])," - " & [SAON_END_NUM] & " ","") & IIf(Not IsNull([SAON_END_SUFFIX]),[SAON_END_SUFFIX] & " ","") & IIF(([PAON_TEXT]= "Abbey Parade" AND [SAON_TEXT] AND [SAON_START_NUM])," ","") & iif(NOT IsNull([PAON_START_NUM])," " & [PAON_TEXT],"")
(ALL OF THIS THE FALSE PART)

Condition and False Part
AND [SAON_TEXT] AND [SAON_START_NUM] these need to be qualified e.g.

AND [SAON_TEXT]='abcd' AND [SAON_START_NUM]>0

Also if these values can be null as implied in your false part then you should wrap them in the nz function where you are not using the isnull function

[PAON_TEXT] should also be wrapped in nz where you are not using the isnull function

The query might be a bit clearer if you swapped the iff statements around a bit e.g.

(IIf(IsNull([SAON_TEXT]),"",[SAON_TEXT] & " ")

You also seem to be missing two brackets right at the end - one for the initial iif statement amdn one for the bracket before the initial iif statement

 

[theborgking1]

Thank you for your help CJ...

Clearly you know your stuff, unlike myself whois still getting to grips with this

Can you clear thinsg up fo rme further please

Why does this not work?

UPDATE GS_LLPG_With_Addresses SET GS_LLPG_With_Addresses.Sub_Addr = (IIF((([PAON_TEXT]= "Abbey Parade" AND [SAON_TEXT] AND [SAON_START_NUM]),[SAON_START_NUM] & " ", (IIf(Not IsNull([SAON_TEXT]),[SAON_TEXT] & " ","")))));

 

[CJ_London]

for the same reason as the other query

AND [SAON_TEXT] AND [SAON_START_NUM] these need to be qualified e.g.

AND [SAON_TEXT]='abcd' AND [SAON_START_NUM]>0

 

[theborgking1]

Okay then want I want to do is qualify by saying..

AND [SAON_TEXT]= I need it to bring back anything which has a value in [SAON_TEXT]

AND [SAON_START_NUM]= I need it to bring back anything which has a value in [SAON_START_NUM]

 

[CJ_London]

AND [SAON_TEXT] is not null AND [SAON_START_NUM] is not null