We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

janandben
janandben asked
on
Medium Priority
777 Views
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 :~)
Comment
Watch Question

Commented:


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.

http://office.microsoft.com/en-us/access-help/set-the-field-size-HA010274716.aspx
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
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.

Sincerely,
Ed
The screenshot below will surely help you figure it out.

Ed
Validation-Rule.jpg
CERTIFIED EXPERT
Top Expert 2016

Commented:
validation rule should be

validation rule    <1000000
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
The reports have a sorting and grouping dialog.
In the query driving the report, you could have created a calculated field
MySortOrder:CLng(WKOrderNum)
And then sorted the report by it.

Then you wouldn't need to change your table
query.jpg
sort.jpg

Commented:
I believe Yobri had it right at http:#a35462268
@Cap

I agree to include the 999999.

Thanks Cap.

Ed

Author

Commented:
Thanks Yobri.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.