Using Multi-Value fields in a query

[timleonard]

I use a query to select several fields from the table including a multi-value field. I then use the "DoCmd.Transfer Spreadsheet acExport acSpreadsheetTy peExcel12" to send the info to an excel.xlsm file. It appears that this method does not like the multi-value fields.

Is there a way to extract or convert the data in the multi-value field to single value during the query?

[jimatqsi]

What do you mean by "multi-value field"

and by
"this method does not like the multi-value fields"?

Jim

[timleonard]

What do you mean by "multi-value field"

and by
"this method does not like the multi-value fields"?

Jim

I have a field in a table that I am using in a query that uses the lookup feature of access 2007 with the display control being a list box and a row source type being a value list. When the query brings in the field it keeps the multi-valued field.

When I try to export the query with this type of field it will give a run-time error '3828' Cannot reference a table with a multi-valed field using an IN clause that refers to another database.

[colintis]

The method of separating the multi-value fields in Access 2007 tables is to append a string ".Value" to the back of the field's name, this should separate the multi-value into multiple rows in its expansion.

For filtering result using the expand method above, you can use INNER JOIN with another table that contains the value you are looking for.

SELECT Issues.Title, Issues.AssignedTo 
FROM [Contacts Extended] INNER JOIN Issues ON 
[Contacts Extended].ID = Issues.AssignedTo.Value 
WHERE ((([Contacts Extended].[Contact Name])= 
"Kelly Rollin"));

For multiple conditions to be included, use OR operator if you are looking for multi-value fields that partly contained your need, using AND operator will only show the records that contain exact values within the multi-value field.