Formatting A Number Field To Show Beginning Zeros

[DataChick]

I have a database in Access 2000 that was set up to house an ID number that is always 4 digits. At the time that we set it up, it was only supposed to house numbers in our department which all start with 1 and now they want to add one that starts with a 0.

The database keeps dropping the initial 0 and making it a 3 digit number...but I need it to be a 4 digit number.

Can anyone tell me how to format a number field to hold the initial 0? This is a released database and is being used by 20 users so I don't want to change the field type and all the links (this is the primary key for this table).

Thank you in advance.

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[SkipVought]

Hi,

I you are talking about a number in a table, ITS A NUMBER! Get over it!

If it's on a report or a query, format to pad with zeros.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[RickSpr]

Formatting only occurs when the data is presented. You can't store the leading zeros unless you make it a text field.

However, you can control the presentation to show leading zeros regardless of where it is displayed. In a table or query datasheet, open it in Design View and set the field's Format property. In a form or report control, set the control's Format property. For a 4-digit number with leading zeros, the property value would be "0000".

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DataChick]

Thank you Rick for your help. I appreciate it.

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[DBLoser]

DataChick,

RickSpr is right and I hope you've already solved this but if not I had to do the same thing last week. I had to format a CHECKNUM field. It had to be 10 wide and have preceeding zeros, here is the code I used in my query:

CheckAmount: Format([CHECKAMT]*100,"0000000000")

The *100 was used to drop the decimal point so you don't need it and the CheckAmount: is just an alias to rename the field when it is displayed so the rest is how you'd do it. All you need to do is remove or add zeros to change the size.

bruce