Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Sep 2008
    Location
    Virginia, USA
    Posts
    27
    Thanks
    2
    Thanked 0 Times in 0 Posts

    IIf statements in query (XP)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIf statements in query (XP)

    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.

  3. #3
    Lounger
    Join Date
    Sep 2008
    Location
    Virginia, USA
    Posts
    27
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: IIf statements in query (XP)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIf statements in query (XP)

    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.
    Attached Images Attached Images
    • File Type: png x.png (5.4 KB, 0 views)

  5. #5
    Lounger
    Join Date
    Sep 2008
    Location
    Virginia, USA
    Posts
    27
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: IIf statements in query (XP)

    That's the answer. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •