extract data from middle of text field in Microsoft access

Posted on 2007-08-02
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
    Select Mid(item_nbr, 3, 3) As cust_dir_nbr...

    LVL 11

    Expert Comment

    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

    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.

    Author Comment


    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


    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

    <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


    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

    Thanks for the patients and the explicit instructions.

    LVL 11

    Expert Comment

    You are most welcome.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    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…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now