Solved

Parse field in access query

Posted on 2014-04-14
9
948 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
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.

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

770 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