Parse data in access query

I need to parse some data in a field in my query but not sure how to do it. It needs to be parsed into 3 separate columns.

The data is structured like this. It won't always be numeric though. The first 6 digits can be alpha-numeric as well. There is always a space after the first 6 characters.

345666 4001
345666 24001


So I need one column to have "345666", another to have the first digit of the next sequence; "4", and then the final piece would be "001".

The second example would be:

"345666"
"24"
"001"

The first 6 characters will always be 6 characters and the last 3 will always be 3 characters. It's the middle that may change the number of characters from 1 to 3 characters.
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this query, change F3 with the actual name of field and Mytable with the actual name of the table


SELECT F3, Left([F3],6) AS Expr1, Trim(Mid([F3],7,Len(Mid([f3],7))-3)) AS Expr2, Right([F3],3) AS Expr3
FROM MyTable;
0
 
Dale FyeCommented:
I would start out with a function:
Public Function fnParse(ParseWhat as string, Pointer as integer) as variant

    Dim myArray() as string

    myArray = Split(ParseWhat, " ")

    if Pointer - 1 < lbound(myArray) Then
        fnParse = Null
    elseif Pointer -1 > ubound(myArray) Then
        fnParse = Null
    else
        fnParse = myArray(Pointer - 1)
    end if

End Function

Open in new window

Then I would write my query something like:

SELECT fnParse([yourField], 1) as Field1
           , Left(fnparse([yourField], 2), Len(fnparse([yourfield], 2)) - 3) as Fiels2
           , Right(fnParse([yourField], 2), 3) as Field3
FROM yourTable
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Perfect! Thank you for your assistance and quick response!
0
 
Dale FyeCommented:
BTW,  I keep fnParse in my library database so I can use it for a wide variety of situations similar to this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.