How to Split Carriage-Return Values into Multiple Rows in Access VBA
Question details
The user needs to parse a single Access database field containing carriage-return-separated text and insert each separated value into its own distinct row.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Attempting to reorganize multi-line text data stored in a single field so that each line becomes a separate record in a table.
- Observed behavior
- Standard Access queries and built-in functions cannot dynamically create multiple records from one field. Attempting to use the Split function directly in a query triggers an 'undefined-function' error or returns only a single row.
Create an empty destination table in your Access database with the exact fields required to store the newly split rows, as the VBA script will need a target location to append the records.
Use a VBA Subroutine and DAO Recordsets
Create a custom VBA subroutine to loop through the source table, split the text using the Split function, and append the results into a destination table.
Because Microsoft Access queries cannot dynamically generate multiple rows from a single record using built-in functions, the parsing process must be handled entirely within a VBA subroutine. Calling a VBA Split function directly inside an Access query will not work because queries cannot natively interpret arrays.
Press Alt + F11 in Microsoft Access to open the Visual Basic for Applications (VBA) editor.
Click Insert in the top menu, then select Module to create a new standard module where the subroutine will be stored.
Write a subroutine and declare your variables. You will need two DAO.Recordset variables: one for reading the source table and one for writing to the destination table.
Open the source recordset and start a Do While Not rs.EOF loop. Inside this loop, use the Split(rs!YourField, vbCrLf) function to break the text into an array based on carriage returns.
Create a nested loop to iterate through the resulting array. Inside this inner loop, use rsDest.AddNew, assign the array element to the target field, and execute rsDest.Update to create the new row.
Close the recordsets in your code, save the module, and run the subroutine directly by pressing F5 or triggering it via a macro or form button.
Manage and Analyze Data Easily with WPS Spreadsheet
While Microsoft Access requires complex VBA scripting and recordset management for tasks like splitting text into rows, handling tabular data is often much easier in a spreadsheet environment. WPS Office offers a free, lightweight, and highly compatible alternative to Microsoft Office, making data formatting and analysis straightforward.
- 1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
- 2. Open Your Data: Export your Access data as a CSV and open it natively in WPS Spreadsheet.
- 3. Split Data Intuitively: Use the Text to Columns feature under the Data tab to easily manipulate and split your text.
Frequently Asked Questions
Why do I get an 'undefined-function error' when using Split in an Access query?
The VBA Split function returns a zero-based, one-dimensional array rather than a standard string. Microsoft Access SQL cannot interpret or unpack arrays directly within a query, which causes it to throw an 'undefined-function' error.
Can I split text into multiple rows using an Access Macro instead of VBA?
No. Standard Access macros lack the advanced array handling and nested recordset looping capabilities required to split delimited text into multiple new records. VBA is strictly required for this specific operation.
What is the correct delimiter to use for carriage returns in VBA?
In VBA, vbCrLf is the constant representing a carriage return followed by a line feed. If your text was generated differently and only uses a carriage return, you can use the vbCr constant instead.
