Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Posts
    55

    Question Unanswered: Access Query with IIF

    I have a query that has historically worked great when I used a single value. I recently changed the source (cbo_SelectQueue) to accept multiple values. Now my queries no longer work. Can anyone help out?

    an example of cbo_SelectQueue results could be 1,2,3 (where each is a separate value). There are many many possible selection choices.

    The intent is that if the field has any of the values the user selected then the field should not be displayed. Otherwise, if the result is not one of the selected, then it should appear on the report.

    TIA

    Current Code:
    FIELDNAME: IIf(([TABLE].[FIELD]=([forms]![FORMNAME]![cbo_SelectQueue])),Null,[TABLE].[QUEUES])

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    what is it returning?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2002
    Posts
    55

    Returning Value

    It runs the query, but the field just shows #Error

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Is the data type for [TABLE].[FIELD] a number? If not then you need quotes around your reference:

    IIf([TABLE].[FIELD]="'" & [forms]![FORMNAME]![cbo_SelectQueue] & "'",Null,[TABLE].[QUEUES])

    Another possibility is you have something misspelled.

  5. #5
    Join Date
    Feb 2002
    Posts
    55
    The value being passed is numeric. The problem is if I pass the value as a literal:

    FIELDNAME: IIf(([MYTABLE].[FIELDNAME]=1) OR ([TABLE].[FIELD]=2),Null,[TABLE].[QUEUES])

    The field will properly null all instances of Field where value is 1 or 2.

    With the code I listed below that passes the identical value, I either get #error or all field values are nulled.

Posting Permissions

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