ACC: How to Parse Comma-Separated Text into Multiple Fields

ID: Q95608

The information in this article applies to:

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you two methods to parse comma-separated text in a Text field and to display the text in multiple Text fields.

You can use the first method for a Text field that contains two words separated by a comma, for example, a field that contains a last name followed by a first name (Smith, John). The method uses an expression in a query that includes three functions: the Instr() function to search for the comma in the Text field, and the Left$() and Right$() functions to extract the two parts of the Text field.

You can use the second method for a Text field that contains more than two words separated by commas, for example, a field that contains a city, a region, and a country (Toronto, Ontario, Canada). This method uses two user-defined functions: a function named CountCSWords() to count the number of comma-separated words in the Text field, and a function named GetCSWord() to return the nth word in the Text field.

MORE INFORMATION

Method 1

To parse a Text field that contains two words separated by a comma, follow these steps:

1. Open any existing database.

2. Create a table with the following structure:

      Table: Parse2Words
      ------------------
      Field Name: Empl
      Data Type:  Text

   three records in the Empl field:

      Smith, John
      Callahan, Laura
      Fuller, Andrew

      Query: QueryTest
      ------------------------------------------------------------------
      Field: FirstName: Right$([Empl],Len([Empl])- InStr(1,[Empl],",")-1)
         Show: True
      Field: LastName: Left$([Empl],InStr(1,[Empl],",")-1)
         Show: True

   NOTE: You can modify the QueryTest query to account for spaces between
   the two parts in the Empl field. For example, if the text in the Empl
   field is "Smith,John" without spaces, remove the -1 from the FirstName
   field expression.

   Empl field into the two fields below:

      FirstName   LastName
      --------------------
      John        Smith
      Laura       Callahan
      Andrew      Fuller

Method 2

This part of the article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

To parse a Text field that contains more than two words separated by commas, follow these steps:

1. Open any database.

2. Create a table with the following structure:

      Table: ParseWords
      --------------------
      Field Name: Location
      Data Type:  Text

   records in the Location field:

      Toronto, Ontario, Canada
      Boston, Massachusetts, USA
      Vancouver, British Columbia, Canada

   if it is not already there:

      Option Explicit

      Function CountCSWords (ByVal S) As Integer
      ' Counts the words in a string that are separated by commas.

      Dim WC As Integer, Pos As Integer
         If VarType(S) <> 8 Or Len(S) = 0 Then
           CountCSWords = 0
           Exit Function
         End If
         WC = 1
         Pos = InStr(S, ",")
         Do While Pos > 0
           WC = WC + 1
           Pos = InStr(Pos + 1, S, ",")
         Loop
         CountCSWords = WC
      End Function

      Function GetCSWord (ByVal S, Indx As Integer)
      ' Returns the nth word in a specific field.

      Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer
         WC = CountCSWords(S)
         If Indx < 1 Or Indx > WC Then
           GetCSWord = Null
           Exit Function
         End If
         Count = 1
         SPos = 1
         For Count = 2 To Indx
           SPos = InStr(SPos, S, ",") + 1
         Next Count
         EPos = InStr(SPos, S, ",") - 1
         If EPos <= 0 Then EPos = Len(S)
         GetCSWord = Trim(Mid(S, SPos, EPos - SPos + 1))
      End Function

7. Create the following query based on the ParseWords table:

      Query: QueryTest2
      ---------------------------------------
      Field: City: GetCSWord([Location],1)
         Show: True
      Field: Region: GetCSWord([Location],2)
         Show: True
      Field: Country: GetCSWord([Location],3)
         Show: True

   Location field into the three fields below:

      City        Region             Country
      --------------------------------------
      Toronto     Ontario            Canada
      Boston      Massachusetts      USA
      Vancouver   British Columbia   Canada

REFERENCES

For more information about parsing text strings, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q115915
   TITLE     : Sample Expressions to Extract Portion of Text String

   ARTICLE-ID: Q148287
   TITLE     : ACC2: NEATCOD2.MDB Available on MSL

   ARTICLE-ID: Q148402
   TITLE     : ACC95: NEATCODE.MDB Available on MSL

Keywords          : kbusage ExrStrg 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998