making all numbers certain size

[masond]

Hey guys



Hope all is well

Sorry to bother you with such a basic question, but I need some help

Every month, I receive an excel file.

Within the excel file, column A, is populated full of numbers. These numbers varies in length

For eg
000001
100002
1111110
5234000
29788881
31111111

Steps
1)What I want to do is import this file into access

2) if there are only 6 digits, add additional zeros on front of the number, so that max number of digits is 10
For eg using the sample data above

100002 is 6 digits long I want it to display as 000 0 100 002
1111110 is 7 digits long I want it to display as 000 1 111 110
5234000 is 7 digits long I want it to display as 000 5 234 000
29788881is 8 digits long I want it to display as 002 9 788 881

From this, I would like to do a right look up (preferably look up 7), to produce a standard length number
0100002
1111110
5234000
9788881

[June7]

Let x represent the field.

Consider:

Format(x, "000 0 000 000")

Mid(Format(x, "00000000"), 2)

[masond]

hi june,
do you have an example you can post up ?

[June7]

Use those expressions in query, textbox ControlSource, or code (macro or VBA). Replace x with the actual field name.