Parse field in access query

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?
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
Dan CraciunConnect With a Mentor IT ConsultantCommented:
MID(field, 1, 6)
FORMAT(MID(field, 7, 3), "0")
MID(field, 10, 3)

HTH,
Dan
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That's perfect! Thank you very much for your help and QUICK response!
0
 
Dan CraciunIT ConsultantCommented:
Glad I could help!
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Dale FyeCommented:
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
 
Dan CraciunIT ConsultantCommented:
Dale, I think the middle number is left padded with spaces.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Yes the middle will always be padded with either 1 or 2 spaces to the left.
0
 
Dale FyeCommented:
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
 
Dan CraciunIT ConsultantCommented:
Nope. 12 chars both
358618  2000
358618 18000

Open in new window

0
 
Dale FyeCommented:
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
All Courses

From novice to tech pro — start learning today.