Results 1 to 2 of 2
2008-11-13, 03:45 #1
- 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)
2008-11-13, 04:12 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 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.