Leading Zeros

[Jamie]

Hello anyone,

I would like to show leading zeros on a numeric field
which ideally is auto numbering.

I want to show 0001 and have this increment with the
creation on every new record.

0001
0002
0003

etc....

This may be easy? the last time I did anything like this
was with dbase IV years ago.

Thanks in anticipation.

Jamie

 

[RobFMS]

Off the top of my head, I believe you have to specify "00000" in the format
section of the table design.

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Guest]

Rob,

Thanks, but ths does not work, any more ideas?

Jamie

 

[Cheryl Fischer]

Jamie,

You can force your numeric fields to display leading zeros in forms, reports
and queries by using:

Format([MyNumberField], "0000") ' which will get you a 4-character
number with leading zeros

However, you cannot store the data in a number field in your table that way.

You can get your number field to increment with the addition of a new record
by making the field an AutoNumber type. But, be aware that the purpose of
an AutoNumber field is only to provide a record identifier with nearly 100%
guarantee of uniqueness. There is no guarantee of consecutiveness, as
AutoNumbers are not re-used; deleting a record or starting to create a new
record will leave 'holes' .

If you must have a number-like field in your table which stores leading
zeros, the Data Type must be text. And, if you must have consecutiveness
guaranteed, you'll need to create and code your own custom numbering system.
This is a frequently-discussed topic and if you do a search on "custom
numbering system in Access" in http://groups.google.com you will get
numerous 'hits'.

 

[Guest]

Thanks for your help Cheryl!

-----Original Message-----
Jamie,

You can force your numeric fields to display leading zeros in forms, reports
and queries by using:

Format([MyNumberField], "0000") ' which will get you a 4-character
number with leading zeros

However, you cannot store the data in a number field in your table that way.

You can get your number field to increment with the addition of a new record
by making the field an AutoNumber type. But, be aware that the purpose of
an AutoNumber field is only to provide a record identifier with nearly 100%
guarantee of uniqueness. There is no guarantee of consecutiveness, as
AutoNumbers are not re-used; deleting a record or starting to create a new
record will leave 'holes' .

If you must have a number-like field in your table which stores leading
zeros, the Data Type must be text. And, if you must have consecutiveness
guaranteed, you'll need to create and code your own custom numbering system.
This is a frequently-discussed topic and if you do a search on "custom
numbering system in Access" in http://groups.google.com you will get
numerous 'hits'.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hello anyone,

I would like to show leading zeros on a numeric field
which ideally is auto numbering.

I want to show 0001 and have this increment with the
creation on every new record.

0001
0002
0003

etc....

This may be easy? the last time I did anything like this
was with dbase IV years ago.

Thanks in anticipation.

Jamie

.

 

[Cheryl Fischer]

You're welcome!

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Thanks for your help Cheryl!

-----Original Message-----
Jamie,

You can force your numeric fields to display leading zeros in forms, reports
and queries by using:

Format([MyNumberField], "0000") ' which will get you a 4-character
number with leading zeros

However, you cannot store the data in a number field in your table that way.

You can get your number field to increment with the addition of a new record
by making the field an AutoNumber type. But, be aware that the purpose of
an AutoNumber field is only to provide a record identifier with nearly 100%
guarantee of uniqueness. There is no guarantee of consecutiveness, as
AutoNumbers are not re-used; deleting a record or starting to create a new
record will leave 'holes' .

If you must have a number-like field in your table which stores leading
zeros, the Data Type must be text. And, if you must have consecutiveness
guaranteed, you'll need to create and code your own custom numbering system.
This is a frequently-discussed topic and if you do a search on "custom
numbering system in Access" in http://groups.google.com you will get
numerous 'hits'.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hello anyone,

I would like to show leading zeros on a numeric field
which ideally is auto numbering.

I want to show 0001 and have this increment with the
creation on every new record.

0001
0002
0003

etc....

This may be easy? the last time I did anything like this
was with dbase IV years ago.

Thanks in anticipation.

Jamie

.