extract data from middle of text field in Microsoft access

Posted on 2007-08-02
Medium Priority
Last Modified: 2013-11-24
How do I get the 3rd 4th and 5th characters from a text field within a access dbase?

I have a table that i have numbers like;

I struggled through some vb code and got it to pull the 00 off the front of the numbers, but I also need to
generate a new number with the 3rd 4th and 5th character.  This is the output i will need to get.
item_nbr                             cust_dir_nbr
12357210011                            357
12184100854                            184
12034380011                            034

I feel luck to have made the vb work for the first part, and have spent all day trying to extract the middle number. Please teach me.

NOTE: the original data has some completely different data in it and it is all in text format.
Question by:marcomnordic
LVL 96

Expert Comment

by:Bob Learned
ID: 19620570
Select Mid(item_nbr, 3, 3) As cust_dir_nbr...

LVL 11

Expert Comment

ID: 19620605
Having stripped the first two zeros off to get (for example) 12357210011 you can get the 3rd through 5th characters with

cust_dir_nbr = Mid("12357210011",3,3)

Which means ' starting at the third character, return three characters from the string.'

But if your data always starts with those two zeros, then you can do this is one step. No need to remove the zeros first...

cust_dir_nbr = Mid("0012357210011",5,3)

.i.e you just start at position 5 and get three characters.

I saw your comment about the original data being in text fields. That's fine ad Mid() is only intended to work with text.



Author Comment

ID: 19620613
Please elaborate, I dont understand as I am but a lowly newby to Access and to VB.

is this going into a ?module? or is it a criteria in a query, should I put it in using the sql view?

Ive seen lots of stuff but dont totaly understand how to make a pretty package with it yet.

I have tried lots of things that seemed like they would work with no success.

Thanks for the help.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 19620683

the data is actually text, but mostly consists of numeric data.  in a few instances the data does not start with 00s and in some others the data contains something like 02841BX1RA-A.

where would i put the

 cust_dir_nbr = Mid("12357210011",3,3)

is that criteria, guessing not, or do I need to fool around with VB? pleas put the training wheels on for me.
LVL 11

Expert Comment

ID: 19620819

No it's not a criteria expression. Your question does not tell us where and why you are doing this, but if it's in a query, for example, then you can build a calculated field using an expression like

cust_dir_nbr : Mid([item_nbr],3,3)

or, assuming you use the original data from your table, and do NOT strip off the first two zeros before hand

cust_dir_nbr : Mid([Original_FieldName],5,3)

where "Original_FieldName" is the name of the table field that has all of the characters in it.

Whichever expression you use, if you type it directly into the Field row in a query design view it will create a calculated field for you called cust_dir_nbr: always providing the query includes the table with the original field in it.

LVL 58

Expert Comment

ID: 19621059
<off-topic comment>


Are you French? I rarely see the French typography that requires a space before the colon. Alas, it's a slight mistake in Access. When you do that, you cannot set any properties for that column (e.g. Caption, try it!). But I think the space is removed if you save, close, and reopen your query.


</off-topic comment>
LVL 11

Accepted Solution

LambertHeenan earned 2000 total points
ID: 19622463

Not French, Scottish and living in New York. But that's an interesting tip you have show us here. OF course I never actually use the space in front of the coln myself. I only put it these so the colon would stand out in the fairly small font that EE uses.


Here is the SQL that would do what I suggest...

SELECT Items_tbl.OriginaField_str, Mid([OriginaField_str],5,3) AS cust_dir_nbr
FROM Items_tbl;

Author Comment

ID: 19624728
Thanks for the patients and the explicit instructions.

LVL 11

Expert Comment

ID: 19624846
You are most welcome.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

864 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