asked on

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

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LindaOKSTATE

ASKER

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...

mbizup

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

LindaOKSTATE

ASKER

Ok, Thank you.

mbizup

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.

parsing string into several pieces in access query -separated by &quot;\&quot;

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