MS Access: Parse a string into two values based on the occurrence of an underscore in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I have a table with a field that I need to separate into two different fields. The field is alpha/numeric in its contents:

Example of the field content is:

1234_ACME company

I need this field split into two fields such as:

Field1: 1234
Field2: ACME company

The Underscore (_) can be in any position in the string.

Answer: In Access, you can create custom functions to supplement what is missing in Access. To do this, click on the Modules tab in the Database window and create a new module.

Then paste in the following custom functions:

Function ParseFirstComp(pValue) As String

    Dim LPosition As Integer

    'Find postion of underscore
    LPosition = InStr(pValue, "_")

    'Return the portion of the string before the underscore
    If LPosition > 0 Then
        ParseFirstComp = Left(pValue, LPosition - 1)
    Else
        ParseFirstComp = ""
    End If

End Function

Function ParseSecondComp(pValue) As String

    Dim LPosition As Integer

    'Find postion of underscore
    LPosition = InStr(pValue, "_")

    'Return the portion of the string after the underscore
    If LPosition > 0 Then
        ParseSecondComp = Mid(pValue, LPosition + 1)
    Else
        ParseSecondComp = ""
    End If

End Function

This first function called ParseFirstComp will return the portion of the string before the underscore. The second function called ParseSecondComp will return the portion of the string after the underscore.

Next, you'll need to use this function in your query.

In the example above, we've used both functions to parse a field called Name. You will need to substitute your field name with the ParseFirstComp and ParseSecondComp functions. So we've typed ParseFirstComp([Name]) in the first field and ParseSecondComp([Name]) in the second field. Access assigns the field name of "Expr1" and "Expr2" - you can always overwrite this.

Now, when we run the query, we'll get the following results: