number formatted for exactly 10 characters, but not displaying the decimal point, AND including the leading zeros.

Mark Drelinger
Mark Drelinger used Ask the Experts™
on
I have a number field in access query that I need to be exactly 10 character long with leading zeros and including the cents, but no decimal.

What I have formatted now:
Amount: Format([GROSS],"00000000.00")
which displays
00000124.58
What I need displayed is
0000012458
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
This seems sloppy, but did appear to work - is there a better way ?

Amount: Format([GROSS],"00000000") & Right(Format([GROSS],0),2)
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Just multiply the value to get the cents:

Amount: Format([GROSS]*100,"0000000000")

Open in new window

Author

Commented:
PERFECT!  Thank you.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial