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.