Parse string question

[Cloonalt]

I have a string that I have to parse - I have to extract data between parenthesis, for example:

City (Baltimore)
City (Philadelphia)

Any help apperciated.

 

[abaldwin]

If the data is always in this format I would suggest the replace function.

Something like

'As you loop through the records or in the query itself do the following. I will stretch it a bit for readability.

dim stString as string

ststring = "City (Baltimore)"

ststring = replace(ststring,"City (","") 'Removes the city and the first space and the first parenthesis.

ststring = replace(ststring,")", "") 'Removes the ending paenthesis.

Of course if you are sadistic in nature you could combine these into one big line with multiple replace commands.




Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Cloonalt]

Thanks, but the data isn't always the same. It just always looks like this:

text (more text)

and I need to extract what's in the parenthesis.

 

[lewds]

Hi...

Could you try something like this?

Private Sub Form_Load()
Dim strTest, strData As String
Dim Pos1, Pos2 As Variant
strTest = "City (CHICAGO)"
Pos1 = (InStr(1, strTest, "(")) + 1
Pos2 = InStr(1, strTest, ")")
strData = Mid(strTest, Pos1, Pos2 - Pos1)
Debug.Print strData
End Sub

strData will be the word CHICAGO

 

[rjoubert]

Or you could do it in one step in your query, using the following expression...

Mid([YourTable]![CityField], InStr(1, [YourTable]![CityField], "(", 1) + 1, Len([YourTable]![CityField])-(InStr(1, [YourTable]![CityField], "(", 1) + 1))

 

[Cloonalt]

Thanks, I'm getting an ODBC error that instr is not a recognized function name.

 

[rjoubert]

What version of Access are you using?

 

[abaldwin]

If you do not have the instr function in your version of access I believe that you can find a file on the web called

vbe6.dll.

Download and put this in your system32 folder overwriting after backing up the one that may be there. It should give you the functions you are looking for.


Andy Baldwin

"Testing is the most overlooked programming language on the books!