• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

parsing string into several pieces in access query -separated by "\"

21\\\\\
 CODES_1ST: Left([CODES_1],InStr([CODES_1],"\")-1)
returns  21


492\717\\\\
CODES_2ND: Mid(Left([CODES_1],InStr(InStr(1,[CODES_1],"\")+1,[CODES_1],"\")-1),InStr(1,[CODES_1],"\")+1)
returns 717

22\495\886\\\
388\668\677\708\\
388\668\677\688\708\

What syntax can i use to get the 3rd, 4th and fifth segments of these strings without the "\"s? Each should go into its own field. I am using Access 2007.

Thanks,
LindaOKState
0
LindaOKSTATE
Asked:
LindaOKSTATE
  • 3
  • 2
1 Solution
 
mbizupCommented:
Add a function to a standard module:

Function GetPartialCode(strData, intPosition) as string
    Dim s() as string
    s = Split(strData,"\")
    GetPartialCode = s(intPosition)
End Function

Open in new window


And call it with columns set up in your query like this:


FirstSegment: GetPartialCode(YourField, 0)
SecondSegment: GetPartialCode(YourField, 1)
ThirdSegment: GetPartialCode(YourField, 2)
FourthSegment: GetPartialCode(YourField, 3)

etc
0
 
LindaOKSTATEAuthor Commented:
Is there not a way to just get it through the query?  I really wasn't wanting to deal with adding functions to a module.  But, if that is the only way...
0
 
mbizupCommented:
You probably could through multiple large expressions in your query, but IMO this is the cleanest/simplest approach (I personally would not want to tackle those statements when the VBA code for this is so streamlined and easy to maintain).
0
 
LindaOKSTATEAuthor Commented:
Ok, Thank you.
0
 
mbizupCommented:
The real benefit of using a function like that from my perspective is that nothing needs to be changed if you need additional or different segments of that data.

The call from your query will always be similar to this:

GetPartialCode(YourField, 0)


... you just need to change the number that represents the 'position'  (zero-based) of the segment you want to return.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now