Text String Manipulation 1

[raa44634]

Importing a canned report into access where I have a string such as:
EQUIPMENT.ACCESSORY LIST.ANALOG.1.CANON.C6412.NP7130.NEW
**or**
PARTS.PARTS.NA.NA.PANASONIC.NA.NA.OEM

I need to break this table field into 8 different query fields. Two fields in the above strings are separted by ".".

First Field was easy using InStr:
CATG_Flex01: Left([tbl_import_IKN_INV]![CATEGORY],InStr([tbl_import_IKN_INV]![CATEGORY],".&quot-1)
where the returned value is "EQUIPMENT"

For example, how would I do the third field (return value would be "ANALOG&quot?
Any help would be appreciated!!! Thx!

 

[jrbarnett]

Hi,

These functions are adapted from the Access 97 Neat Code database to allow a custom delimiter and I find them very useful for this sort of thing.

You can use them in a loop to extract individual items using the dot as a separator.
Go to your database window, the modules tab, click New and copy and paste the code below in it:

Function CountCSVWords(strString As String, strDelimiter As String) As Integer
'
' Counts words in a string separated by commas.
' Adapted from MS Neat Code 97 database.
Dim WC As Integer, Pos As Integer
  WC = 1
  Pos = InStr(strString, strDelimiter)
  Do While Pos > 0
    WC = WC + 1
    Pos = InStr(Pos + 1, strString, strDelimiter)
  Loop
  CountCSVWords = WC
End Function

Function GetCSVWord(strString As String, Indx As Integer, strDelimiter As String)
'
' Returns the <Indx>th word from a comma-separated string.
' For example, GetCSVWord(&quot;Nancy, Bob&quot;, 2, &quot;,&quot;) returns Bob.
' Adapted from Microsoft Access 97 Neat Code database by
' J Barnett.
' 
Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer

  WC = CountCSVWords(strString, strDelimiter)
  If Indx < 1 Or Indx > WC Then
    GetCSVWord = Null
    Exit Function
  End If
  Count = 1
  SPos = 1
  For Count = 2 To Indx
    SPos = InStr(SPos, strString, strDelimiter) + 1
  Next Count
  EPos = InStr(SPos, strString, strDelimiter) - 1
  If EPos <= 0 Then EPos = Len(strString)
  GetCSVWord = Mid(strString, SPos, EPos - SPos + 1)
End Function

Save this as modStrings

Now you can put code similar to the following in your database to extract individual items:

For i = 1 To CountCSVWords (Fieldname, ".")
  Debug.Print GetCSVWord (Fieldname, ".", i)
Next i

John

 

[raa44634]

Worked beautifully! Thanks a million!
Tony