Solved

Parse field in access query

Posted on 2014-04-14
9
929 Views
Last Modified: 2014-04-14
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
Comment
Question by:Lawrence Salvucci
  • 4
  • 3
  • 2
9 Comments
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 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

by:Lawrence Salvucci
ID: 39999648
That's perfect! Thank you very much for your help and QUICK response!
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39999653
Glad I could help!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39999669
Dale, I think the middle number is left padded with spaces.
0
 
LVL 1

Author Comment

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

Expert Comment

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

Expert Comment

by:Dan Craciun
ID: 39999726
Nope. 12 chars both
358618  2000
358618 18000

Open in new window

0
 
LVL 47

Expert Comment

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 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

16 Experts available now in Live!

Get 1:1 Help Now