extract data from middle of text field in Microsoft access

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.
Who is Participating?
LambertHeenanConnect With a Mentor Commented:

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;
Bob LearnedCommented:
Select Mid(item_nbr, 3, 3) As cust_dir_nbr...

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.


Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

marcomnordicAuthor Commented:
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.
marcomnordicAuthor Commented:

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.

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.

<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>
marcomnordicAuthor Commented:
Thanks for the patients and the explicit instructions.

You are most welcome.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.