Parse a comma delimited field into seperate fields (MS ACCESS VBA 2003)

Question

I inherited a database where user input fields are stored as a comma delimited string. I know. Lame. I want a way to parse these fields in a SELECT query where there are three segments of varying number of characters. Counter to all the recommendations that I insert fields into a new table or create a stored procedure to do this, this is what I came up with. I'm wondering if anyone sees any flaw in doing this as a select query (where I can easily convert from string to parsed and back again as need be).

Field_A
5,25,89

So to get the left segment, which is the most straightforward:

Field_1: Left$([Field_A],InStr([Field_A],",")-1)

To get the right-most segment:

Field_3: Right$([Field_A],Len([Field_A])-InStrRev([Field_A],","))

Middle segment was the trickiest:

Field_2: Mid([Field_A],InStr([Field_A],",")+1,InStrRev([Field_A],",")-InStr([Field_A],",")-1)

So the result is:

Field_1   Field_2   Field_3
5           25        89

Any consenting opinions?

Answer 1

Well, if you insist on going down this road...... This might be easier and more adaptable. Create a function in a module:

Public Function GetValueFromDelimString(sPackedValue As String, nPos As Long, 
                                        Optional sDelim As String = ",")

Dim sElements() As String

sElements() = Split(sPackedValue, sDelim)
If UBound(sElements) < nPos Then
    GetValueFromDelimString = ""
Else
    GetValueFromDelimString = sElements(nPos)
End If

End Function

Now in your query you can get any field in the string like this: GetValueFromDelimString([MultiValueField],0) AS FirstElement, GetValueFromDelimString([MultiValueField],1) AS SecondElement, etc.

I feel like I am buying beer for a minor, encouraging this type of behavior :)