Extract strings

[latex88]

I have a field in a table that I'd like to extra the texts from. The are stored in below format (separated by colons). They don't always the same amount of characters and not all of them have the same amount of texts, some may only have Text1:Text2::, while some may have Text1:::Text4.

Text1:Text2:Text3:Text4

 

[plog]

You will need to use the Mid function (http://www.techonthenet.com/access/functions/string/mid.php) to extract a substring, the InStr function (http://www.techonthenet.com/access/functions/string/instr.php) to find the colons and determine what the starting and ending points of the substring are and you will need to use a loop (http://msdn.microsoft.com/en-us/library/ezk76t25.aspx) to control how often your extraction code runs.

If you run into any issues, post back here with your code and we can help you out.

 

[latex88]

Thanks for the quick reply. I was afraid of that. I was hoping for one function that may do the trick I'm not that seasoned of a programmer. It may take me hours if not days, but I'll give it a go.

 

[pbaldy]

You might also explore the Split() function, which will split all the values into an array you can either loop through or get values from.

 

[missinglinq]

Paul's suggestion of using Split() is right on the mark! Here's a very simple example:

Assuming that WholeName = "Anderson,Jack"

Components = Split(WholeName, ",")

FirstName = Components(1) Jack

LastName = Components(0) Anderson

Linq ;0)>

 

[redalert]

Here is a function I use a lot as splitting strings like this is a common procedure:

Public Function fncSemiGroup(strString, strDelim, intOption)
Dim varArray As Variant

If intOption = 0 Then
        fncSemiGroup = ""
ElseIf InStr(1, strString, strDelim) > 0 Then
    varArray = Split(strString, strDelim)
    If intOption > UBound(varArray) + 1 Then
        fncSemiGroup = ""
    Else
        fncSemiGroup = varArray(intOption - 1)
    End If
Else
        fncSemiGroup = ""
End If
End Function

Call it like this:

strString="Text1:Text2:Text3:Text4"
Msgbox fncSemiGroup(strString, ":",1)

will give you "Text1"

You will have to have a loop:

For i = 1 to x  'where x is number of elements in the string
      strResult = fncSemiGroup(strString, ":",1)
      'Code here to do whatever you need to do
Next

 

[latex88]

Thank you all for your input. For now, I incorporate Linq's suggestion. It works for now when the data consist finite number of components, but in the future, if additional components are added, then I would have to adjust the code.

 

[redalert]

A good principle is to build in scalability from the start, it is often easier
and certainly saves time later. You may not be around when the system needs changing in the future and others may not even be aware that changes are / were needed. This may result in incorrect decisions being made which are based upon inaccurate / incomplete information / data.

 

[missinglinq]

...if additional components are added, then I would have to adjust the code...

You can determine the number of components each time the code runs by counting them, using code like this:

ComponentsNumber = Len([YourString]) - Len(Replace([YourString], ",", "")) + 1

Linq ;0)>