Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Parse field in access query

Posted on 2014-04-14
Medium Priority
1,063 Views
I need to be able to split a field into 3 different sections in my access query.

The first section will always be the first 6 characters in the field.
The second section will always be the last 3 characters in the field
And the third part will be what's in the middle. The middle will consist of either 1, 2, or 3 characters to the right and 1 or two spaces to the left. I need to drop the spaces and then pull the characters that are left which can be either 1, 2, or 3 characters.

Examples:

358618 18000 = Parsed would be: "358618" "18" "000"
358618  2000 = Parsed would be: "358618: "2" "000"

How can I do this?
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
• 4
• 3
• 2

LVL 35

Accepted Solution

Dan Craciun earned 2000 total points
ID: 39999640
MID(field, 1, 6)
FORMAT(MID(field, 7, 3), "0")
MID(field, 10, 3)

HTH,
Dan
0

LVL 1

Author Closing Comment

ID: 39999648
That's perfect! Thank you very much for your help and QUICK response!
0

LVL 35

Expert Comment

ID: 39999653
0

LVL 48

Expert Comment

ID: 39999662
That might work for the example you provided, but it will not work for all cases, since you have a varying number of characters in your string.

Left(FieldName, 6)
Trim(Mid(FieldName, 7, Len(FieldName])-9))
Right(FieldName, 3)
0

LVL 35

Expert Comment

ID: 39999669
Dale, I think the middle number is left padded with spaces.
0

LVL 1

Author Comment

ID: 39999674
Yes the middle will always be padded with either 1 or 2 spaces to the left.
0

LVL 48

Expert Comment

ID: 39999707
but:

MID(field, 7, 3)

Will always give you 3 characters

and in the 2nd example, there are only 11 characters, so

MID(field, 10, 3)

will give you:

"00"

not

"000"
0

LVL 35

Expert Comment

ID: 39999726
Nope. 12 chars both
358618  2000
358618 18000
0

LVL 48

Expert Comment

ID: 39999989
OK, proportional spacing got me on that one, but the description the OP provided made it sound like there were a variable number of characters in the middle section.
0

Featured Post

Question has a verified solution.

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

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 hâ€¦
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process fromâ€¦
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
Suggested Courses
Course of the Month10 days, 6 hours left to enroll