How to Convert Access Multi-Value Fields to Text for Excel Export
Question details
The user needs to combine multiple selected values from an Access multi-valued field into a single text string so the data can be exported to Excel using a SELECT INTO query.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Exporting Access database records containing multi-valued fields to Excel or CSV formats.
- Observed behavior
- Access blocks the direct use of multi-valued fields in SELECT INTO statements, resulting in errors and preventing straightforward data export.
Before modifying your Access queries or writing custom VBA code, ensure you have backed up your current database to prevent any accidental data corruption or loss.
Use a VBA Function to Convert Multi-Value Fields to Delimited Text
Write a custom VBA function to iterate through the underlying records of the multi-valued field and concatenate the selections into a single comma-separated text string.
Because Microsoft Access stores multi-valued fields as hidden related tables, they cannot be directly flattened into a new table via a SELECT INTO query. Using a custom VBA function allows you to read those hidden values and output them as standard text, which can then be easily exported.
Open your Access database and press ALT + F11 to launch the Visual Basic for Applications (VBA) Editor.
Click 'Insert' from the top menu and select 'Module'. Paste a CSV-conversion VBA function designed to concatenate multi-value fields (such as those provided by Access MVPs like TheDBGuy).
Save the module and return to your Access query design view. Replace the direct reference to the multi-valued field with an expression that calls your new VBA function, passing the record ID as a parameter.
Run the query to verify that the field now displays as a single text string. Once confirmed, you can safely use this query in your SELECT INTO statement for exporting to Excel.

Open and Analyze Your Exported Database Data with WPS Office
Once you have successfully exported your Access data into a CSV or Excel file, WPS Spreadsheet offers a free, lightweight, and highly compatible environment to clean, analyze, and format your exported data without needing a paid Microsoft Office subscription.
- 1. Open WPS Spreadsheet: Launch the WPS Office suite on your computer and select the Spreadsheet application.
- 2. Import the Exported File: Go to Menu > Open and select the CSV or Excel file you just exported from your Access database.
- 3. Analyze and Format Data: Use built-in tools like 'Text to Columns' or 'Find and Replace' to further organize the concatenated multi-value strings as needed.

Frequently Asked Questions
Why does Access prevent SELECT INTO queries with multi-valued fields?
Access treats multi-valued fields as hidden related tables behind the scenes. Because a SELECT INTO query attempts to create a flat table structure, it cannot automatically translate this hidden one-to-many relationship, resulting in a system error.
Are there pre-built functions for concatenating multi-valued fields in Access?
Yes, many Access developers use well-known community scripts, such as Allen Browne's 'ConcatRelated' function or TheDBGuy's 'SimpleCSV' function, to extract and concatenate values from the underlying tables of a multi-valued field.
Will the concatenated text be fully compatible when opened in Excel?
Yes. Once the multi-valued field is converted into a standard comma-separated text string using a query expression, Excel will read it as a normal text cell during the export, preserving the data exactly as it was concatenated.




