zero numbers

[Chi]

Hi,

In my table, I use Number Type for the ClinicNumber field.

I entered 0001525. However, as soon as I move the cursor to different
places, the 000 disappear.

Ex: 0001525 will be 1525.

Would you please help me to fix the problem or keep the 0 numbers?

Thanks
Chi

 

[akphidelt]

I would just change the type to "Text"

Hopefully you will not have to perform any mathmatical calculations with
zero placeholders.

 

[Jeff Boyce]

"Numbers" are digits you plan to add, subtract, multiply & divide.

Are you planning to do that with your "Clinic Numbers"? (I kind of doubt
it!)

Call them "text" instead, since you are using the characters that happen to
be digits to identify your Clinics.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Chi]

Hi Akphidelt,
Thank you for the quick respond. However, I need the Number Type. Any other
ideas is really appreciated.

Chi

 

[akphidelt]

Well without any explanation of what you are doing with this value it is hard
to give any other ideas. I assume you need a number because you must be doing
some ID calculations. In that case you can use the number format in the
table, but you will not have the 3 leading zeros. You can though create a
query and concatenate the zeros on to the value.

 

[Ken Sheridan]

Your need for a number data type implies that ClinicNumber has some sort of
ordinal or cardinal property in relation to the entity type (Clinics?) of
which it is an attribute. Quite why you need the leading zeros is unclear,
but if that's what you want simply format the values as 0000000 in a query,
form or report. You can do this by setting the Format property in the
properties sheet, or by using the Format function in an expression used for a
computed column in a query or a computed control in a form or report.

While you can format a column in table design view I'd advise against it.
Data should only ever be entered via forms and viewed via forms or reports,
never in raw datasheet view. So there is no need whatsoever for formatting
the column per se. A form can of course be in datasheet view, however.

Ken Sheridan
Stafford, England

 

[Dominic Vella]

Generally, it always best to put numbers (such as phone numbers) into text
fields unless you are specifically doing some calculations on it.

In forms and reports, you can put 0000000 into the format property of the
text boxes. Whilst not encouraged, you could also put the 0000000 into the
format property in the tables ClinicNumber field.

If you're using it in a programming manner, you would use:
=Format([ClinicNumber],"0000000")

Dom

 

[Dominic Vella]

Yes, but invoice numbers, model numbers, some postcodes and other
registration numbers can have leading zeros. Oh my, I just noticed my bank
BSB number also has a leading zero.

:O)
Dom

But, the bottom line is 'real numbers' don't have leading zeros.

Bob

Generally, it always best to put numbers (such as phone numbers) into text
fields unless you are specifically doing some calculations on it.

In forms and reports, you can put 0000000 into the format property of the
text boxes. Whilst not encouraged, you could also put the 0000000 into
the
format property in the tables ClinicNumber field.

If you're using it in a programming manner, you would use:
=Format([ClinicNumber],"0000000")

Dom

Hi,

[quoted text clipped - 9 lines]

Thanks
Chi

 

[Rob Parker]

Yes, but those "numbers" are not "real numbers", in that you cannot (or,
better, do not) perform arithmetical operations on them. You don't add up a
column of invoice numbers, or BSBs, or whatever. They are identifiers,
which happen to use a subset of the numeric characters; in a database, it's
generally better to store them as text. If you store them as numeric
fields, you CAN perform numeric operations on them, but the results will
always be meaningless; and you'll be forced to use a Format function to
convert them to text to display as required.

Rob

Yes, but invoice numbers, model numbers, some postcodes and other
registration numbers can have leading zeros. Oh my, I just noticed
my bank BSB number also has a leading zero.

O)

Dom

But, the bottom line is 'real numbers' don't have leading zeros.

Bob

Generally, it always best to put numbers (such as phone numbers)
into text fields unless you are specifically doing some
calculations on it. In forms and reports, you can put 0000000 into the
format property
of the text boxes. Whilst not encouraged, you could also put the
0000000 into the
format property in the tables ClinicNumber field.

If you're using it in a programming manner, you would use:
=Format([ClinicNumber],"0000000")

Dom

Hi,

[quoted text clipped - 9 lines]
Thanks
Chi

 

[BruceM]

Invice numbers are typically incremented, which means a mathematical
operation along the lines of DMax(...) + 1 is used to determine the next
number. In that case the integer may be formatted with leading zeros for
display purposes. The same could hold true of model number, serial numbers,
and so forth, depending on the business rule (and provided they do not
contain non-numeric characters. It simplifies assigning a unique number for
invoice, etc. if it can be done automatically.
Having said that, mathematical operations can often be performed on text
fields. However, addition is treated as concatenation when combining two
fields, so such operations must be approached with great care. If
mathematical operations are to be performed, and if there are no non-numeric
characters, a number field makes perfect sense.

Yes, but those "numbers" are not "real numbers", in that you cannot (or,
better, do not) perform arithmetical operations on them. You don't add up
a column of invoice numbers, or BSBs, or whatever. They are identifiers,
which happen to use a subset of the numeric characters; in a database,
it's generally better to store them as text. If you store them as numeric
fields, you CAN perform numeric operations on them, but the results will
always be meaningless; and you'll be forced to use a Format function to
convert them to text to display as required.

Rob

Yes, but invoice numbers, model numbers, some postcodes and other
registration numbers can have leading zeros. Oh my, I just noticed
my bank BSB number also has a leading zero.

O)

Dom

But, the bottom line is 'real numbers' don't have leading zeros.

Bob

Dominic Vella wrote:
Generally, it always best to put numbers (such as phone numbers)
into text fields unless you are specifically doing some
calculations on it. In forms and reports, you can put 0000000 into the
format property
of the text boxes. Whilst not encouraged, you could also put the
0000000 into the
format property in the tables ClinicNumber field.

If you're using it in a programming manner, you would use:
=Format([ClinicNumber],"0000000")

Dom

Hi,

[quoted text clipped - 9 lines]
Thanks
Chi