How to parse comma separated text into Multiple Fields?

[Leonard]

Sponsored Links

Hi friends,
I want to know about the methods that can be useful for analyzing comma separated text into a text field. And also with that I want to display text in multiple text fields. I think that I have explained in simple language so that there would be no confusion for you guys.!! So please tell me how to parse comma separated text into Multiple Fields?

[HellBoy21]

You can use the first method for a text field that contains two words separated by a comma, for example, a field containing a name followed by last name (Joa, Alice). The method uses an expression in a query that has three functions: Instr () to find the comma in the text field and the Left $ () and Right $ () to extract the two parts of the text field. You can use the second method to a text field that contains more than two words separated by commas, for example, a field containing a city, region and country. This method uses two user-defined functions: a function called CountCSWords () to count the number of words separated by commas in the text field and a feature called GetCSWord () to return the word n in the text field.

[Sam.D]

To analyze 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

3. See table Parse2Words in Datasheet view and type the following three records used in the field :
   

   Diaz, Belina
   Peire, Laura 
   Fuller, Andrew

4. Then you will have to create query, based on the table Parse2Words.
5. Run the query.

[Eric B]

You can write the following query, which is based on the table Parse2Words. you can include this in 4th step that was mentioned by the 'Sam.D'. The following is the code :

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

[Glenny]

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

1. Open any database.
2. View the ParseWords table in Datasheet view, and then type any three records into that.
3. Create a module and type the following line in the Declarations section if not already there :
   Option Explicit
4. After writing an appropriate code, compile the module, save it as basp and close it.
5. Run the query.

[Raine]

The Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. This method uses two user-defined functions: a function called CountCSWords () to count the number of words separated by commas. The following code would be useful for you if using the method that is explained above by 'Glenny'. The code should be like below :

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

       Sun 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

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

       Sun WC As Integer, Count As Integer, SPOS As Integer, As Integer Epos
          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