Microsoft Access Help

marc_howarth · 1 Apr 2006 at 22:33

Hi there,

I am trying to write an IIF expression in a query in access so far i have got:

Code:

Expr1: IIf([Marc],"Marc",IIf([Andy],"Andy",IIf([Marc] & [Andy],"Both","")))

I know this is not correct as this never shows the word "Both". The Fields [Marc] and [Andy] are both yes/no feild. I'm guessing i've messed up here:

Code:

IIf([Marc] & [Andy],

Would be grateful if someone could point me in the right direction

thanks in advance

Marc

Naqi · 1 Apr 2006 at 23:55

Try

Code:

IIF(AND([Marc],[Andy]),

The & symbol is used to join strings together.

marc_howarth · 2 Apr 2006 at 01:03

Thanks for your reply but if i type in that code it says:

The expression you enetered contains invalid syntax.

You may have entered a comma without a preceding value or identifier

I'm not too sure what this is saying, any more help would be very appreciative

thanks again

Marc

Naqi · 4 Apr 2006 at 06:21

Right, try this, Ive tested it and it works

Code:

Expr1: IIf([Marc],"Marc",IIf([Andy],"Andy",IIf(([Marc] AND [Andy]),"Both","")))

Fezzer · 4 Apr 2006 at 09:42

You need to do the AND test first. "Both" will never show doing it in your original order because for the [Marc] AND [Andy] to evaluate to TRUE the first IIf test must also evaluate to TRUE stopping the test ever getting to the AND test. (Sorry not very well explained

)

Try

IIf([Marc] AND [Andy],"Both",IIf([Marc],"Marc",IIf([Andy],"Andy","Neither")))

marc_howarth · 4 Apr 2006 at 11:49

thank you ever so much, works perfectly now, both those comments really helped

Naqi · 5 Apr 2006 at 06:27

Fezzer said:
You need to do the AND test first. "Both" will never show doing it in your original order because for the [Marc] AND [Andy] to evaluate to TRUE the first IIf test must also evaluate to TRUE stopping the test ever getting to the AND test. (Sorry not very well explained )..................

Good thinking batman