Issues with IIF Statement

[datachick]

I am having issues with this IIF statement below in Access 2010:


this is the ordering of the IIf so that they will be assigned in that order; however, the problem is that I get a #Error when i try this IIf statement.
The tables are joined on Field A


Value: IIf([[Table1].[FieldA]in("89812","83849","83847"),"a",
IIf([[Table1].[FieldA]="91245","a",
IIf([[Table1].[FieldB] = "102","b",
IIf([[Table1].[FieldA]="91164","c",
IIf([[Table1].[FieldA]="39649","d",
IIf([Table2].[FieldC]="f","f","e"))))))))

[ranman256]

don use IIF,
use table to 'translate' these values

[Field],[value]
91245 a
102 , b

etc... this table in the query will give you the value without IFs and complex sql.

[John_G]

Numerous syntax errors.

1 - Why do you have [[ (double opening square brackets) before Table1? There is no need for them, and they result in unbalenced [] pairs.
2 - Your () pairs are not balanced - you have 8 closing ) when you should only have 6.

If the values for FieldA and FieldB in Table1 are numeric type, you should not have quotation marks around the values in the Iif expression.

[ranman256]

no, theres no need for them, but it just helps define a field.

[John_G]

It's the double [[ that I was referring to; the single ones are fine and do indeed provide clarity.

[[Table1].[FieldA] should be [Table1].[FieldA] - only one opening [.

[Mahendra1000]

I would like to go on with John_G,

I think it is due to the syntax error. The open brackets doesn't paired

[LaughingBull]

Try Select Case here is the youtube link https://www.youtube.com/watch?v=2P0GWYySu0Q

[June7]

I can't get the IN operator to work in expression.

Consider:

Switch(InStr("89812,83849,83847,91245",[FieldA])>0,"a", [FieldB]="102","b", [FieldA]="91164","c", [FieldA]="39649","d", [FeldC]="f","f", True,"e")

[LaughingBull]

try this video It seem to do what you are looking for https://www.youtube.com/watch?v=Zx0itIyaDuk