Microsoft KB Archive/95608

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

Article ID: 95608

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q95608



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



SUMMARY
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.



Method 1
To parse a Text field that contains two words separated by a comma, follow these steps:  Open any existing database.  Create a table with the following structure:      Table: Parse2Words --     Field Name: Empl Data Type: Text  View the Parse2Words table in Datasheet view and type the following three records in the Empl field:

Smith, John

Callahan, Laura

Fuller, Andrew

  Create the following query based on the Parse2Words table:      Query: QueryTest --     Field: FirstName: Right$([Empl],Len([Empl])- InStr(1,[Empl],&quot;,&quot;)-1) Show: True Field: LastName: Left$([Empl],InStr(1,[Empl],&quot;,&quot;)-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 &quot;Smith,John&quot; without spaces, remove the -1 from the FirstName field expression.   Run the query. Note that the QueryTest query separates the text in the Empl field into the two fields below: <pre class="fixed_text">     FirstName   LastName John       Smith Laura      Callahan Andrew     Fuller </li></ol>

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 &quot;Building Applications with Microsoft Access&quot; 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 &quot;Introduction to Programming&quot; manual in Microsoft Access version 1.x or the &quot;Building Applications&quot; manual in Microsoft Access version 2.0.

To parse a Text field that contains more than two words separated by commas, follow these steps: <ol> Open any database.</li>  Create a table with the following structure: <pre class="fixed_text">     Table: ParseWords Field Name: Location Data Type: Text </li> View the ParseWords table in Datasheet view and type the following three records in the Location field:

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

</li> Create a module and type the following line in the Declarations section if it is not already there:

Option Explicit

</li>  Type the following procedures: 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, &quot;,&quot;) Do While Pos > 0 WC = WC + 1 Pos = InStr(Pos + 1, S, &quot;,&quot;) 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, &quot;,&quot;) + 1 Next Count EPos = InStr(SPos, S, &quot;,&quot;) - 1 If EPos <= 0 Then EPos = Len(S) GetCSWord = Trim(Mid(S, SPos, EPos - SPos + 1)) End Function </li> Compile the module, save it as basParse, and close it.</li>  Create the following query based on the ParseWords table: <pre class="fixed_text">     Query: QueryTest2 ---     Field: City: GetCSWord([Location],1) Show: True Field: Region: GetCSWord([Location],2) Show: True Field: Country: GetCSWord([Location],3) Show: True </li>  Run the query. Note that the QueryTest2 query separates the text in the Location field into the three fields below: <pre class="fixed_text">     City        Region             Country --     Toronto     Ontario            Canada Boston     Massachusetts      USA Vancouver  British Columbia   Canada </li></ol>

<div class="references_section">