LEADING ZEROS

  • Thread starter Thread starter William
  • Start date Start date
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?
 
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

(no e-mails, please!)


KARL DEWEY said:
You can format your display for leading zeros - Format([YourField],
"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?
Click to expand...
Click to expand...
 
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:
You can format your display for leading zeros - Format([YourField], "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?
Click to expand...
Click to expand...
 
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:
You can format your display for leading zeros - Format([YourField], "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?
Click to expand...
Click to expand...
Click to expand...
 
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:
You can format your display for leading zeros - Format([YourField],
"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?
Click to expand...
Click to expand...
Click to expand...
 
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:
You can format your display for leading zeros - Format([YourField], "00000").
--
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top