Select a part of a string

[Bruno]

Hello,

I have a string of characters like "NP,VY,21SPC,A,PNCPCY,VFB,1750" or
"NP,VY,ECO315,A,OVEXCY,VBG,55" and I would like to isolate the characters
between 3rd and 4th comma knowing that the length of characters may vary like
in the 2 examples provided.

Thanks,
Bruno

 

[strive4peace]

Hi Bruno,

here is some code written by John Viescas:

extract the "nth" part of a string based on the delimiter you supply.

Public Function ParseString( strIn As String, _
Optional intPart As Integer = 1, _
Optional strChar As String = " ") As String
'----------- --------- --------- --------- --------- --------- --
' Inputs: String to parse, Part to return, Delimiter
' Outputs: The "nth" part or "chunk" of the string
' Created By: JLV 05/15/2007
' Last Revised: JLV 05/15/2007
'----------- --------- --------- --------- --------- --------- --
' strIn - the string to parse
' intPart - the part you want returned;
' default is 1 if you don't supply it
' strChar - the delimiter character;
' default is a blank if you don't supply it
' NOTE: This is a rewrite of Parse using the Split function
' Place to put the result

Dim varStrings As Variant
' Internal variable for part and delimiter character
Dim intP As Integer, strDelim As String

'Get the position number
intP = intPart
' Make sure it's >= 1
If intP < 0 Then intP = 1
' Get the delimiter
strDelim = Left(strChar, 1)
' Now parse it
varStrings = Split(strIn, strDelim)
' Check that position exists in array
If intP > UBound(varStrings) + 1 Then
' Ooops - return empty string
ParseString = ""
Else
' Return the "nth" piece
ParseString = varStrings(intP - 1)
End If

End Function
'~~~~~~~~~~~~~~~~~~~~~~~~`

Here are the directions:

Put the ParseString code in a general module. Compile and Save

In a query, to display the second part of a string separated by spaces
in a Select Query, use:

field --> PartOne: ParseString( [MyStringField] , 1, ",")

field --> PartTwo: ParseString( [MyStringField] , 2, ",")

etc


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Klatuu]

You can use the following function in your query to return the value. Use a
calculated field:

SomeField: ThirdPosition([YourFieldName])

Public Function ThirdPosition(InputString As String) As String
Dim varArray As Variant

varArrary = Split(InputString, ",")
If Ubound(varArray) < 3 Then
ThirdPosition = vbNullstring
Else
ThirdPosition = varArry(3)
End If

End Function

 

[Bruno]

Thanks for your help, it works now. I've just needed to slightly correct your
solution: varArray(2) instead of Var Array(3) because it was giving me the
4th part of the string.

I have corrected it by iteration as I am not very familiar with modules and
that level of Access.

Bruno

You can use the following function in your query to return the value. Use a
calculated field:

SomeField: ThirdPosition([YourFieldName])

Public Function ThirdPosition(InputString As String) As String
Dim varArray As Variant

varArrary = Split(InputString, ",")
If Ubound(varArray) < 3 Then
ThirdPosition = vbNullstring
Else
ThirdPosition = varArry(3)
End If

End Function


--
Dave Hargis, Microsoft Access MVP

Hello,

I have a string of characters like "NP,VY,21SPC,A,PNCPCY,VFB,1750" or
"NP,VY,ECO315,A,OVEXCY,VBG,55" and I would like to isolate the characters
between 3rd and 4th comma knowing that the length of characters may vary like
in the 2 examples provided.

Thanks,
Bruno

 

[Klatuu]

Glad you got it working, but I am surprised it needs to be element 2. You
original post said between the 3rd and fourth comma:
0 1 2 3 4 5 6 <-Array elements
NP,VY,21SPC,A,PNCPCY,VFB,1750
1 2 3 4 5 6 <-Commas

So:
varArray(0) = NP
varArray(1) = VY
varArray(2) = S1SPC
varArray(3) = A
varArray(4) = PNCPCY
varArray(5) = VFB
varArray(6) = 1750

But, if 2 is correct, be sure to also change the line that checks the Ubound
of the array.
--
Dave Hargis, Microsoft Access MVP

Thanks for your help, it works now. I've just needed to slightly correct your
solution: varArray(2) instead of Var Array(3) because it was giving me the
4th part of the string.

I have corrected it by iteration as I am not very familiar with modules and
that level of Access.

Bruno

You can use the following function in your query to return the value. Use a
calculated field:

SomeField: ThirdPosition([YourFieldName])

Public Function ThirdPosition(InputString As String) As String
Dim varArray As Variant

varArrary = Split(InputString, ",")
If Ubound(varArray) < 3 Then
ThirdPosition = vbNullstring
Else
ThirdPosition = varArry(3)
End If

End Function


--
Dave Hargis, Microsoft Access MVP

Hello,

I have a string of characters like "NP,VY,21SPC,A,PNCPCY,VFB,1750" or
"NP,VY,ECO315,A,OVEXCY,VBG,55" and I would like to isolate the characters
between 3rd and 4th comma knowing that the length of characters may vary like
in the 2 examples provided.

Thanks,
Bruno