Solved

Parse data in access query

Posted on 2013-06-10
4
356 Views
Last Modified: 2013-06-10
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.
0
Comment
Question by:Lawrence Salvucci
  • 2
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
Comment Utility
Perfect! Thank you for your assistance and quick response!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
BTW,  I keep fnParse in my library database so I can use it for a wide variety of situations similar to this.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now