Parsing Text in MS Access

Question

I have column that contains strings. The strings in that column look like this:

FirstString/SecondString/ThirdString

I need to parse this so I have two values:

Value 1: FirstString/SecondString Value 2: ThirdString

I could have actually longer strings but I always nee it seperated like [string1/string2/string3/...][stringN]

What I need to end up with is this:

Column1: [string1/string2/string3/etc....] Column2: [stringN]

I can't find anyway in access to do this. Any suggestions? Do i need regular expressions? If so, is there a way to do this in the query designer?

Update: Both of the expressions give me this error: "The expression you entered contains invalid syntax, or you need to enclose your text data in quotes."

expr1: Left( [Property] , InStrRev( [Property] , "/") - 1), Mid( [Property] , InStrRev( [Property] , "/") + 1)

expr1: mid( [Property] , 1, instr( [Property] , "/", -1)) , mid( [Property] , instr( [Property] , "/", -1)+1, length( [Property] ))

Answer 1

mid(col, 1, instr(col, "/", -1)) , mid(col, instr(col, "/", -1)+1, length(col))

Answer 2

In a query, use the following two expressions as columns:

Left(col, InStrRev(col, "/") - 1), Mid(col, InStrRev(col, "/") + 1)

col is your column.

If in VBA, use the following:

last_index= InStrRev(your_string, "/")

first_part= Left$(your_string, last_index - 1)
last_part= Mid$(your_string, last_index + 1)

Answer 3

Is there any chance you can fix the underlying data structure to be properly normalized so that you can avoid the problem in the first place? Along with retrieving the data comes a whole host or problems with maintaining it accurately, and that would all be ameliorated if you weren't storing multiple values in a single field.

--
David W. Fenton
David Fenton Associates

Answer 4

I know you're trying to do this inside a query so the SQL string functions are probably your best bet.

However, it's worth mentioning that there's a regular expression COM object accessible from VBA. Just add a reference to the Microsoft VBScript Regular Expressions library inside of your macro code.

Then you can do stuff like this

Dim szLine As String
Dim regex As New RegExp
Dim colregmatch As MatchCollection

With regex
    .MultiLine = False
    .Global = True
    .IgnoreCase = False
End With

szLine = "FirstString/SecondString/ThirdString"

regex.Pattern = "^(.*?\/.*?)/(.*?)$"
Set colregmatch = regex.Execute(szLine)

'FirstString/SecondString
Debug.Print colregmatch.Item(0).submatches.Item(0)
'ThirdString
Debug.Print colregmatch.Item(0).submatches.Item(1)