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

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:


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.
Lawrence Salvucci
Lawrence Salvucci
  • 2
1 Solution
Rey Obrero (Capricorn1)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;
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
        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
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Perfect! Thank you for your assistance and quick response!
Dale FyeCommented:
BTW,  I keep fnParse in my library database so I can use it for a wide variety of situations similar to this.
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.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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