---

MS Access 2003: Parse a string into two values based on the occurrence of an underscore

This MSAccess tutorial explains how to parse a string into two values based on the occurrence of an underscore in Access 2003 (with screenshots and step-by-step instructions).

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: