468,539 Members | 1,737 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

Using Multi-Value fields in a query

I use a query to select several fields from the table including a multi-value field. I then use the "DoCmd.TransferSpreadsheet acExport acSpreadsheetTypeExcel12" 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?
Jun 19 '10 #1
3 10167
jimatqsi
1,255 Expert 1GB
What do you mean by "multi-value field"

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

Jim
Jun 19 '10 #2
@jimatqsi
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.
Jun 19 '10 #3
colintis
255 100+
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Issues.Title, Issues.AssignedTo 
  2. FROM [Contacts Extended] INNER JOIN Issues ON 
  3. [Contacts Extended].ID = Issues.AssignedTo.Value 
  4. WHERE ((([Contacts Extended].[Contact Name])= 
  5. "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.
Jun 20 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Graham Blandford | last post: by
3 posts views Thread by simchajoy2000 | last post: by
3 posts views Thread by Jesus Suarez | last post: by
2 posts views Thread by stuartornum | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.