Format an AutoNumber Field

[gheyman]

Can you format the AutoNumber field, in a table, to be 5 characters?

Example: The number 1 would show as 00001.

 

[Russell Hauf]

Not sure why you'd need to format it in a table. If you are exposing this to users, you can format the value in a form or report. But an autonumber field is always going to be a ... yep, number.

 

[Joe4]

You can apply the Format function to it in a Query calculated field or VBA like:

Format([ID],"00000")

There shouldn't be any need to apply a custom format to it in the underlying table, as users should not be going directly in to the Table to access of view the data anyway.

 

[gheyman]

Thanks. Are you saying I can only do this in a query or VB and I cannot format the field in the Table properties?

PS. The formatting question isn't limited to formatting an Autonumber field

 

[Russell Hauf]

That's right - for a number field. You should never need to format a field in a table, as Joe says above. You can format anything you want for the users in a form / report / query. I'd guess I'd ask why you feel like you need to format a field in a table?

 

[gheyman]

I created a new field that pulls the AutoNumber in and in that fields format I put 00000

That seems to be working

Thanks

 

[Norie]

You can format an AutoNumber field, just put 00000 (or whatever) in the Format section of the field's properties while in design mode.

 

[xenou]

+ think about whatever might happen when numbers get bigger than 99999 (if that is possible). Also, whether a number is 00001 or 99999 it's not a particularly pleasant format

EDIT I.e., if you get numbers gibber than 99999 with a format of "00000" I think they will still display correctly (probably as 100000, 1000001, ...). But why are you using a five character format is the question - and I assume the answer is that you expect numbers never to exceed five digits. And as stated above, pretty much you should not care about the format of a primary key autonumber field in a table because that's not something users should ever look at directly in a table itself. But if that format carries through to all your queries, forms and reports maybe you can make the case that it will be convenient.

EDIT: sp>bigger