Data Import & Export

How to Convert Access Multi-Value Fields to Text for Excel Export

Kushani NimanthikaKushani Nimanthika Oct 13, 2026 869 views

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.

How to Convert an Access Multi-Value Field to Text for Export
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 you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Open your Access database and press ALT + F11 to launch the Visual Basic for Applications (VBA) Editor.

2
Insert a New Module

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).

3
Call the Function in Your Query

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.

4
Run and Export

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.

Use a VBA Function to Convert Multi-Value Fields to Delimited Text
Query Performance: Using VBA functions row-by-row inside an Access query can slow down execution on very large datasets. Test the query performance on a small batch of records first.
Free Microsoft Office alternative

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. 1. Open WPS Spreadsheet: Launch the WPS Office suite on your computer and select the Spreadsheet application.
  2. 2. Import the Exported File: Go to Menu > Open and select the CSV or Excel file you just exported from your Access database.
  3. 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.
100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Free and lightweight suite for PC, Mac, and mobile devicesFamiliar ribbon interface makes data analysis and formatting intuitiveBuilt-in advanced data tools to split, filter, and organize concatenated text
microsoft office alternative - wps office

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.