top of page

Parsing Data in an Access Column Into Multiple New Columns

The Tip of the Night for last night, showed how to parse data in an Access column. The code I posted specifically acted to pull data to the left of a delimiter. Here's how to parse out the data on both sides of a delimiter into two new columns.

In an example like this one, we have city and state names separated with tilde delimiters.

In the SQL mode of a query design view enter this code:

SELECT Table2.Field1, Trim(querysplit([Field1],"~",0)) AS Field2, Trim(querysplit([Field1],"~",1)) AS Field3 FROM Table2; We modify the number in the Trim function by entering a number to indicate the sequence of the text after a delimiter that we want to pull (so, 'querysplit([Field1],"~",1) pulls the first string after the ~ delimiter.)

We combine the two Trim functions by simply putting a comma between them on the line of code beginning with SELECT.

Note that as explained in last night's tip we need to have the following VBA code inserted in a module in order for this query to work:

Function QuerySplit(FieldName As String, Delim As String, Position As Integer) QuerySplit = Split(FieldName, Delim)(Position) End Function

When this query is run we end up with this result with the city and state names parsed into separate columns.

bottom of page