leading zeros in autonumber

[genedoc]

Hi, I'm an old SQL/FoxPro type, and the help here is great for dragging me
kicking and screaming into the foibles of Access!

My question: can I force a fixed-format onto an Autonumber field? I need
the autonumber to display on a report with leading zeros, e.g. "000016." Do
I need to convert it to text first? Not sure of syntax for that.

Thanks!

 

[Klatuu]

Well, your first error is using an autonumber field for anything meaningful.
You cannot depend on what the value may be. If set as incrementing by 1,
you may think they are sequential, but be aware there will be gaps.
Autonumber fields really should only be used as surrogate primary keys and
to be used as the foreign key value in child records.

But, as you should know if you are coming from the antiquity of FoxPro,
numeric fields do not have leading zeros. In Access, you can use the Format
function to present leading zeros for a numeric value. BTW, autnumber
fields are actually a Long Integer (int in SQL Server)

=Format([MisusedAutonumberField], "00000")

I started with DBASE in the '80s and went from there to FoxBase and on to
FoxPro until '98
Great database engine (IMHO)

 

[genedoc]

Hi, never mind, found the answer in another thread response from Vinson on
8/22/2007. Just force the format in the properties to "000000". Thought the
formats were restricted to the drop-down box. We're having fun now!

 

[Klatuu]

Glad you found what you need.
Formats can be used just about anywhere, but don't get confused between the
Format function and the Format property for form/report controls or table
fields.

The Format function styles data you want to present.
The Format property is about the same with different syntax, but it does not
affect how the data are stored. It only formats it for presentation.

 

[John W. Vinson]

Hi, never mind, found the answer in another thread response from Vinson on
8/22/2007. Just force the format in the properties to "000000". Thought the
formats were restricted to the drop-down box. We're having fun now!

Well, I'll agree with Dave/Klatuu that you should emphatically NOT use an
Autonumber field in any way which makes it meaningful to the user. I keep them
"under the hood".

 

[genedoc]

Understood. It's an easy-peasy protoyper; users will be shielded. Thanks
again!