IIf Expression

[SteveE]

I having some trouble trying to use an expression in my query,
I have 3 indivisual exoressions in 3 fields I which work perfectly, however I want only 1 field produced so I tried placing 1 after the other as below:

Sector: IIf([SpacePos]=3,Left([PostalCode],[SpacePos]) & Mid([PostalCode],[SpacePos]+1,1)),IIf([SpacePos]=4,Left([PostalCode],[SpacePos])+"" & Mid([PostalCode],[SpacePos]+1,1)),IIf([SpacePos]=5,Left([PostalCode],[SpacePos]-1) & Mid([PostalCode],[SpacePos]+1,1))

I have also tried Or inbetween them but no luck either, I get the "contains invalid syntax" errors etc.
Any advise appricated

 

[namliam]

I have no clue what so ever why you are doing this or what your goal is... but you need to do it like so:

Sector: 
IIf([SpacePos]=3
   ,Left([PostalCode],[SpacePos]) & Mid([PostalCode],[SpacePos]+1,1)
   ,IIf([SpacePos]=4
       ,Left([PostalCode],[SpacePos])+"" & Mid([PostalCode],[SpacePos]+1,1)
       ,Left([NewPostCode],[SpacePos]-1) & Mid([NewPostCode],[SpacePos]+1,1)
       ) 
   )

You see ? Instead of putting one after another you have to "include" the second Iif into the fist.

The 3rd Iif is to much, it doesnt have an else. So if the first to fail, the third one is the only one left over.

GL

 

[SteveE]

Great, and thanks for your help I now understand just where I was going wrong, works perfectly now!!

Many thanks again
Steve