Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Parse data in access query

Posted on 2013-06-10
Medium Priority
417 Views
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
Question by:Lawrence Salvucci
[X]
###### 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
• 2

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 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 48

Expert Comment

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

ID: 39235844
Perfect! Thank you for your assistance and quick response!
0

LVL 48

Expert Comment

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

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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â€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦
###### Suggested Courses
Course of the Month10 days, 4 hours left to enroll