CODEWITHSUNDEEP
vbacsvms-access
TSS
TSS

Reputation: 176

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

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?

Upvotes: 5

Views: 33208

Answers (3)

vedran
vedran

Reputation: 1

This is an old thread, but someone might search it. You can also do the same strategy as an update query. That way you can keep the original CSV and have 3 new destination fields that can be calculate and recalculated depending on your application purposes.

Upvotes: 0

Peter Lake
Peter Lake

Reputation: 338

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 :)

Upvotes: 6

Lokerim
Lokerim

Reputation: 338

It sounds like you're not asking for information on how to parse a comma-delimited field into different fields, but rather looking for people to support you in your decision to do so, yes?

The fact, as you've already discovered, is that you can indeed do this with skillful application of functions in your SQL field definitions. But that doesn't mean that you should.

In the short run, it's an easy way to achieve your goals as data manager, I'll grant you that. But as a long-term solution it's just adding another layer of complexity to what seems like a poorly-designed database (I know that the latter is not your fault -- I too have inherited my share of "lame" databases).

So I applaud you on "getting the job done" for now, but would advise you to listen to "all the recommendations that you insert fields into a new table" -- they're good recommendations. It'll take more planning and effort, but in the long run you'll have a better database. And that will make everything you do with it easier, faster, and more reliable.

Upvotes: 0

Related Questions