W

#### William

Is there a way to force leading zeros in Access while still retaining the
format as number. I can do this in Excel easily enough
(Format/Cells/Custom/specify number of digits in Type window). Surely there
must be a way to do this in Access?

W

#### William

Just a clarification. If I type in 12345, I want it to show as 012345.

K

D

#### Douglas J. Steele

The Format function converts the number to a string, though.

It's possible simply to set the Format property of the field, but I'm
unclear as to what possible benefit William thinks will be realized by so
doing.

--
Doug Steele, Microsoft Access MVP

KARL DEWEY said:
"00000").
--
Build a little, test a little.

William said:
Is there a way to force leading zeros in Access while still retaining the
format as number. I can do this in Excel easily enough
(Format/Cells/Custom/specify number of digits in Type window). Surely
there
must be a way to do this in Access?

W

#### William

I must be doing something wrong with your solution. I open the tbl in
design, go to the Format row and enter what you have written below. I
substitute the name of the field, but all I get is Format([SixDigitCode],
"00000") followed by my original numerical entry. E.g., ([YourField],
"00000") 1234.
--
William

KARL DEWEY said:
--
Build a little, test a little.

William said:
Is there a way to force leading zeros in Access while still retaining the
format as number. I can do this in Excel easily enough
(Format/Cells/Custom/specify number of digits in Type window). Surely there
must be a way to do this in Access?

L

#### Lynn Trapp

Karl's suggestion would be something you do in a query. You can simply put 6
zeros -- 000000 -- in the format property of your table field.

--
Lynn Trapp

William said:
I must be doing something wrong with your solution. I open the tbl in
design, go to the Format row and enter what you have written below. I
substitute the name of the field, but all I get is Format([SixDigitCode],
"00000") followed by my original numerical entry. E.g., ([YourField],
"00000") 1234.
--
William

KARL DEWEY said:
--
Build a little, test a little.

William said:
Is there a way to force leading zeros in Access while still retaining the
format as number. I can do this in Excel easily enough
(Format/Cells/Custom/specify number of digits in Type window). Surely there
must be a way to do this in Access?

J

#### Jeff Boyce

I'm with Doug ... why? What business need will you be satisfying by
displaying a 5-digit number with a leading zero?

If the 5-character string is just that, a string of characters that happen
to be numeric characters, rather than a number (*something you "do math"
on*), your table definition needs to call it a Text field, not a Numeric
field.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

William said:
I must be doing something wrong with your solution. I open the tbl in
design, go to the Format row and enter what you have written below. I
substitute the name of the field, but all I get is Format([SixDigitCode],
"00000") followed by my original numerical entry. E.g., ([YourField],
"00000") 1234.
--
William

KARL DEWEY said:
"00000").
--
Build a little, test a little.

William said:
Is there a way to force leading zeros in Access while still retaining
the
format as number. I can do this in Excel easily enough
(Format/Cells/Custom/specify number of digits in Type window). Surely
there
must be a way to do this in Access?

W

#### William

Oooh! And that's exactly where I need it. Thanks
--
William

Lynn Trapp said:
Karl's suggestion would be something you do in a query. You can simply put 6
zeros -- 000000 -- in the format property of your table field.

--
Lynn Trapp

William said:
I must be doing something wrong with your solution. I open the tbl in
design, go to the Format row and enter what you have written below. I
substitute the name of the field, but all I get is Format([SixDigitCode],
"00000") followed by my original numerical entry. E.g., ([YourField],
"00000") 1234.
--
William

KARL DEWEY said:
--
Build a little, test a little.

:

Is there a way to force leading zeros in Access while still retaining the
format as number. I can do this in Excel easily enough
(Format/Cells/Custom/specify number of digits in Type window). Surely there
must be a way to do this in Access?