Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Parse data in access query

Posted on 2013-06-10
4
390 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39235825
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)
ID: 39235842
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
ID: 39235844
Perfect! Thank you for your assistance and quick response!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39235849
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

837 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