Formatting currency


Jen Scott

I need to take a field that currently has a decimal place in it and change it
to remove the decimal, but retain the digits after the decimal (including
zeros) -- I also need to maintain negative/positive:


123.45 should display as 12345
12.00 should display as 1200
-987.01 should display as -98701

I've tried using the format function in a query, but haven't had any luck.
I'm using Access 2007. Any help is appreciated! Thanks!



John Spencer


REPLACE(SomeField & "",".","")

Concatenating a zero-length string to the number field changes it into a
string. Replace removes the period from the string if there is one.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Dirk Goldgar

Jen Scott said:
I actually need to format it a little differently -- see below:

Are you formatting this for display/printing only? If so, how about
mutiplying it by 100 and formatting that:

Format([YourField] * 100, "000000000")

Do you have to concern yourself with negative values?


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question