IIf statements in query (XP)

[spqapat]

1. What is the limit to the number of nested IIf statements in a calculated query? Is there a limit to the number of conditions within an IIf statement?

2. I am getting the following error message when running a select query with a calculated field:

The expression you entered contains invalid syntax

You may have entered an operand without an operator

I have edited this calculated field trying to identify the problem, but I continue to get this error message with the field being as simple as:

test: IIf([lname]="smith","A","B")

When I save this language it changes the syntax to: test: IIf([lname]="smith",["A"],["B"])

When I run the query with this field I am asked for parameters for A and B (of course)

Please help.

[HansV]

1) You can nest IIFs up to seven levels deep in an expression. If you have lots of similar conditions, consider using the Switch functionor Choose function.

2) The syntax of your expression is correct. I copied and pasted it into a query in my test database and changed the field name to the name of a field in the table the query is based on. The expression worked as intended.

Are you sure that you've used "straight" quotes? Access doesn't understand "curly" quotes.

[spqapat]

I'm not familiar with variation in quotation marks. How does one find/create either?

[HansV]

Microsoft Word has an option (in Tools | AutoCorrect Options..., AutoFormat As You Type tab) to automatically replace straight quotes with curly quotes. See below for the result.
Some people like to compose expressions in Word, then copy/paste them into Access. This could cause the problem.

[spqapat]

That's the answer. Thanks.