How do I set the “Field Size” for a 6 digit number in a Field on a Table?

Posted on 2011-04-25
Last Modified: 2013-11-28
In my table, I have a WKOrderNum field and the End User inserts a 6 digit number in the form.  Initially, I had the Field in the Table setup as a “Data Type” Text and the “Field Size” 6, but I needed to change the “Data Type” to a  Number  because of the Reports that I created required Sorting in  numerical order. For example if, the WKOrderNum field was “3456727”, “3572”, and “3456728”,  the results were “3456727”, “3456728” and “3572”.  Therefore, I change the WKOrderNum field to a “Number” Data Type and the “Field Size” as an Integer” .  This resulted with too many digits.   Problem: How do I set the “Field Size” for a 6 digit number?  
I am a novice, so please give me details :~)
Question by:janandben
    LVL 9

    Expert Comment


    1.Right-click the table that contains the field that you want to change, and then click Design View.

    2.In the table design grid, select the field for which you want to change the field size.

    3.In the Field Properties pane, on the General tab, enter the new field size in the Field Size property. You can enter a value from 1 to 255. This number specifies the maximum number of characters that each value can have. For larger text fields, use the Memo data type.

     Note   For data in a Text field, Access does not reserve space beyond what is necessary to hold actual values. The Field Size property is the maximum field value size.
    LVL 23

    Accepted Solution

    Make the field a Text Data Type.

    The Field Size should be 6.

    The Input Mask should be ######.
    LVL 19

    Expert Comment

    You may use the Validation Rule combined with Validation Text of the Table Property.

    Type <999999 in the Validation Rule Row of the General Property Tab and "This field only holds 6 digits" in Validation Text.

    LVL 19

    Expert Comment

    The screenshot below will surely help you figure it out.

    LVL 119

    Expert Comment

    by:Rey Obrero
    validation rule should be

    validation rule    <1000000
    LVL 26

    Expert Comment

    The reports have a sorting and grouping dialog.
    In the query driving the report, you could have created a calculated field
    And then sorted the report by it.

    Then you wouldn't need to change your table
    LVL 44

    Expert Comment

    I believe Yobri had it right at http:#a35462268
    LVL 19

    Expert Comment


    I agree to include the 999999.

    Thanks Cap.


    Author Closing Comment

    Thanks Yobri.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    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…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    761 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

    6 Experts available now in Live!

    Get 1:1 Help Now