Putting a zero (0) before a number

[Alex Martinez]

Hi,

I am using Access 2002 and I am trying to put accounts numbers in a field
call account number, the only problem I have is that I am unable to put
017108, all I get is 17108. my field is a number and is a double type. How
can I have 017108 show in my table? Any help will be appreiciated. Thank
you.

 

[Rick Brandt]

Hi,

I am using Access 2002 and I am trying to put accounts numbers in a
field call account number, the only problem I have is that I am
unable to put 017108, all I get is 17108. my field is a number and
is a double type. How can I have 017108 show in my table? Any help
will be appreiciated. Thank you.

A number is stored as a number. You can use a format property to display
leading zeros if all numbers should display as the same length.

EX: 017108, 017109, 000171, etc..

If you need to either...

Save different lengths with leading zeros...

0123, 00456, 000789, etc..

....or store values like 0123 and 00123 and have the database treat them as
different entries then you need to use a text DataType instead of a Numeric one.

 

[Vincent Johns]

A number is stored as a number. You can use a format property to display
leading zeros if all numbers should display as the same length.

EX: 017108, 017109, 000171, etc..

[...]

Specifically, here's an example of how to do that. Suppose you have a
Table, [Number], with the following values in field [Number].[Number]:

Number
------
23
17108

Then you can define a Query, [Q_Formatted], as follows:

SELECT Number.Number,
Format$([Number]![Number],"000000") AS Formatted
FROM [Number]
ORDER BY Format$([Number]![Number],"000000");

and in Query Datasheet View it will look like this:

Number Formatted
------ ---------
23 000023
17108 017108


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[peregenem]

I am using Access 2002 and I am trying to put accounts numbers in a field
call account number, the only problem I have is that I am unable to put
017108, all I get is 17108. my field is a number and is a double type.

Is this really a 'number' e.g. do you want to perform math on it? I
think you'll find it is fixed length text e.g.

CREATE TABLE Accounts (
account_nbr CHAR(6) NOT NULL,
CONSTRAINT account_nbr_pattern
CHECK (account_nbr LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
CONSTRAINT account_nbr_leading_zero
CHECK (LEFT(account_nbr, 1) = '0')
.... other columns ...
)

You could combine the check for a leading zero into the pattern but I'd
recommend keeping it separate e.g. easy to DROP later.

 

[roccogrand]

Alex,

As peregenem suggested, does your account number need to be a number in as
much as you will never want to do math with it?

When I had this possible recently, I made the account number, in my case a
contract line item number, a text field. I set the field size to 6, and the
format to 000000 in the table definition.

The form shows the leading zeros and so does the report. This requires no
coding.

LDN

I am using Access 2002 and I am trying to put accounts numbers in a field
call account number, the only problem I have is that I am unable to put
017108, all I get is 17108. my field is a number and is a double type.

Is this really a 'number' e.g. do you want to perform math on it? I
think you'll find it is fixed length text e.g.

CREATE TABLE Accounts (
account_nbr CHAR(6) NOT NULL,
CONSTRAINT account_nbr_pattern
CHECK (account_nbr LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
CONSTRAINT account_nbr_leading_zero
CHECK (LEFT(account_nbr, 1) = '0')
.... other columns ...
)

You could combine the check for a leading zero into the pattern but I'd
recommend keeping it separate e.g. easy to DROP later.

 

[Shep]

Change the account number Data Type to Text and only allow numbers to be
entered by specifying an input mask as 999999 use the number of nines equal
to the maximum number of digits in an account number. Do not allow
duplicates. Be careful to note that 0123 and 123 are different account
numbers.

I am using Access 2002 and I am trying to put accounts numbers in a field
call account number, the only problem I have is that I am unable to put
017108, all I get is 17108. my field is a number and is a double type.

Is this really a 'number' e.g. do you want to perform math on it? I
think you'll find it is fixed length text e.g.

CREATE TABLE Accounts (
account_nbr CHAR(6) NOT NULL,
CONSTRAINT account_nbr_pattern
CHECK (account_nbr LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
CONSTRAINT account_nbr_leading_zero
CHECK (LEFT(account_nbr, 1) = '0')
... other columns ...
)

You could combine the check for a leading zero into the pattern but I'd
recommend keeping it separate e.g. easy to DROP later.

 

[peregenem]

Change the account number Data Type to Text and only allow numbers to be
entered by specifying an input mask as 999999

Will this 'input mask' really prevent illegal changes to the data? e.g.


UPDATE TheTable SET account_number = 'blah'

I think the OP needs a CHECK constraint (a.k.a. Validation Rule) i.e.
at the data engine level to stop bad data form getting into the
database.