Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Washington, District Of Columbia, USA
    Thanked 0 Times in 0 Posts

    Multivalued Fields in Queries (Access 2007)

    I'm trying to refer to a multivalued field in a IIF statement in a query, but I keep getting the error that its not valid in the expression. The expression worked fine before I converted the field to multivalued. I've also tried making the relevant field into an expression in its own right and then using that in the IIF statement, but I get the same error. Advice?

    Etc: (IIf(IsNull([Notes]),"",[Notes] & " ")) & (IIf(IsNull([MCC]),"","MCC: " & [MCC])) (MCC is the multivalued field, and I want it to show all the values selected for the record, rather than just an individual value which is what I get when I substitute MCC.Value)


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Multivalued Fields in Queries (Access 2007)

    If you use a multivalued field by itself, you can specify whether you want to see all values in one row, separated by commas, or in separate rows. But I don't think you can use multivalued fields in expressions like this, VBA doesn't know how to handle them.

    Multivalued fields are a very bad idea anyway, in my opinion.

Posting Permissions

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