Specify exact number of digitals/letters in a field

[Gunilla]

I have a table where three of the fields have to be an exact number of characters. E.g in one of the fields I have 23456 but I need it to read
0023456. The format is Text.

 

[vbaInet]

I think the Format property should be 0000000

 

[SOS]

And you'll have to format in other places as well to retain the formatting. Some things like reports do not retain table level formatting (nor query level, etc).

 

[Gunilla]

No this does not work. I don't understand why. I just have to fill up with 0's from the left. I am just working with the table could that be the problem.

 

[dkinley]

If a field in a table, click on the field and in the General tab under Format use: 000000

If a control on a form/report you can use: 000000;0; in the input mask field.

If in a query you will have to add another column, declare a variable and use the following form: xName: Format([FieldName],"000000")

Hope that helps,

-dK

 

[ChrisO]

>>I am just working with the table could that be the problem.<<

It could be because users of the database generally don’t get access to the table.
Why do you feel the need to do this and why store formatted numbers in a text field?

 

[Gunilla]

I am exporting to a text-file that will be imported to another database and the field property in that database is string and 7 characters.

 

[dkinley]

If you use a query to drive the export instead of the table, you can use ....

If in a query you will have to add another column, declare a variable and use the following form: xName: Format([FieldName],"000000")

Except add another 0 to make it 7 characters.

-dK

 

[Galaxiom]

Have you considered storing the values in the table as a number and padding with zeros for display only? The user should never see the table.

This would work best if the numbers were assigned by the application rather than entered by the user.

Unlike a text field, with a number field you can simply set the Format property of the controls to 0000000.