Format Numeric 9(8)V99

Question

Experts -

I have a requirement to format a currency field in the above format.  For example, the value $10783.05 would be formatted as 0001078305. 

Any idea what the simplest way of working this into a query is? 

Thank you!

---

Bonediggler

Answer 1

This seems to work:



Format(CStr(Replace([Amount],".","")),"0000000000")

---

Bonediggler

Answer 2

First replace the dot with nothing. Then format the result with enough zeros to meet your requirements.

format(replace([myField],".",""),"0000000000")

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 3

I think it would be somewhat more efficient to use this:

    Format([YourCurrencyField]*100,"0000000000")

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

I think it would be somewhat more efficient to use this:

    Format([YourCurrencyField]*100,"0000000000")

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Dirk - maybe so. For one thing, the field does not have to be converted to a string to replace the decimal point when doing it your way.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 5

First replace the dot with nothing. Then format the result with enough zeros to meet your requirements.

format(replace([myField],".",""),"0000000000")

Hi Bill,

I get wrong results with this function when myField is of type Currency, and not yet formatted to 2 decimal places.

Imb.

Edit: if the second decimal place is  0.

Answer 6

First replace the dot with nothing. Then format the result with enough zeros to meet your requirements.

format(replace([myField],".",""),"0000000000")

Hi Bill,

I get wrong results with this function when myField is of type Currency, and not yet formatted to 2 decimal places.

Imb.

Edit: if the second decimal place is  0.

Thanks for pointing that out, Imb. I was only going by the specs that the OP provided. And you are absolutely right. The decimal place must be established at 2 places. If it can vary from 0-4 then we must inspect each value as it comes in. Doing this format is actually very unrealistic unless the number of decimals is set up ahead of time.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 7

I think it would be somewhat more efficient to use this:

    Format([YourCurrencyField]*100,"0000000000")

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

I vote on Dirk Goldgar's answer due to the problems of reading number format instead of currency.

This will not show the actual number of decimal places.

Format(CStr(Replace([Amount],".","")),"0000000000")

$7.50 will be 0000000075

$15.00 will be 0000000015

But Dirk's version will place the 2 decimal points behind a number.

Dirk's version will be...

0000000750

0000001500

In other word, be careful with conversion. Someone might see $7.50 as $75.00 or as $0.75

Answer 8

I vote on Dirk Goldgar's answer due to the problems of reading number format instead of currency.

Hi AccessVandal,

That is also what I answered to Bill: the input value should have 2 decimals to give correct results.

That brings me also to a refinement of the answer of Dirk. As currency values "can" have 4 decimals, you could also add a rounding after the multiplication with 100. But this depends on whether this rounding (e.g. after percent calculations) is already done in some other part of the program.

Imb.

Answer 9

There's actually a flaw in my method, if the currency amount could exceed 99,999,999.99 .  In that case, the output of the format function will have more than 10 character positions, and so will not conform to the 9(8)V99 specification.  That's because Format() will automatically add the overflow digits.

For example, if the currency amount was $123,456,789.00, then we would get this in the Immediate window:

    c = 123456789.00@ : ?Format(c * 100,"0000000000")
    12345678900

But "12345678900" is 11 digits, not 10.

To protect against this, without actually checking for the overflow, we could apply the Right() function to the output of the Format() function, like this:

    c = 123456789.00@ : ?Right(Format(c * 100,"0000000000"), 10)
    2345678900

Of course, that loses the information that an overflow occurred.  To capture that fact, one would have to use code along the lines of:

    Dim formattedDigits As String
    Dim overflowOccurred As Boolean

    formattedDigits = Format(currencyValue * 100,"0000000000")
    If Len(formattedDigits) > 10 Then
        formattedDigits = Right(formattedDigits, 10)
        overflowOccurred = True
        ' or else raise an error
    End If

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 10

As I said, This formatting is not practical unless the amount is strictly controlled so it is never more than 10 digits including the 2-place decimal. I can't think of a reason do do this as it is not necessary in either fixed-width or delimited output. But I'm sure Bonedigger has his/her reasons.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 11

Dirk,

It would not be a flaw if you use this method instead of "0" zeros.

Format(currencyValue * 100, "##")

Use the Hash symbol instead of "0" zeros.

That would solved the of left side numbers.

However, if the currency had 4-place decimals it will not work.

In most currency cases, 2-place decimals are more common. It's rare to see a value of less than 1 cent. Not a common practice that I know of.

Answer 12

Dirk,

It would not be a flaw if you use this method instead of "0" zeros.

Format(currencyValue * 100, "##")

Use the Hash symbol instead of "0" zeros.

That would solved the of left side numbers.

However, if the currency had 4-place decimals it will not work.

In most currency cases, 2-place decimals are more common. It's rare to see a value of less than 1 cent. Not a common practice that I know of.

Vandal - your formula is not padding the left with zeros as was requested. That's why we originally went with the zeros.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 13

Bill,

It does and I have tested it. Works the same with Zeros.

My point is that the left side numbers are not a problem but the right side, 2 or 4 decimals is a problem if it is not define correctly a in record.

If you do this instead of the Right() function with the Left() and Len() function, you get the left side numbers correctly like....

Left(format_value, Len(format_value) - 2)

assuming a 2-place decimal currency.... say the fomated value is 12345678900, the output is 123456789, omitting the 00 which is the 2-place decimal.

You now use the Right() function to get the 2-place decimal ... Right(format_value, 2). From here, your code will concatenate the values with a correctly place decimal position. 

Answer 14

Maybe I'm not testing properly. I tried using the hash marks like this:

format (1078305,"##########")
and format(1078305,"##")

Result: 1078305

Using the zeros: format (1078305,"0000000000")

Result: 0001078305

That's why you must use the zeros in the string. And there never was an expressed possibility of 4 decimal places.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 15

Bill,

I had remove the Replace() function and Dirk’s Multiplication from the existing reply. It’s my mistake to think that others might understand the code.

A single function alone would not work like this Format(CurrencyValue, “0”).

The reason is that Access removes the zeros behind the decimal point. Like….

123456789.00……..  you would get     123456789   without the zeros.

With Dirk’s code… Format(“123456789.00” * 100, “0”)     you get 12345678900

Which is correct to as it places the decimal behind the last zero.

The confusion is the Format function by placing multiple zeros as a requirement which is not necessary for a currency value.

Dirk’s code might not work in some cases in a query or control but the method works if you know how and understood the concept.

In Controls, you might need to modify the code. You will need 2 Format() and 1 Replace() function. Example…

=Format(Replace(Format([ControlName],"#.00"),".",""),"0")

The trick is the inner Format() that will change the decimal behaviour. Since formatting with remove the decimal and zeros, the inner Format will place the decimal and the Replace() will remove the decimal with a blank. Thereby the outside Format(), you will get 12345678900.

Dirk’s code will also work as well.

=Format([ControlName] * 100, “0”)

You should get the same results as mention above.

Your method will also work but Dirk’s method is much simpler.

The overflow as describe by Dirk is not an error because currency value will always exceed the format digits requirements so long as you provide the actual decimal position when you need to revert back.

Come to think about it, Dirk’s code will tell you how many decimal places were in the formatting. Whatever the digits formatting requirements in currency are completely redundant but acceptable as it ignores the formatting when the value exceeds the format. You only need to know the decimal place required.

I understood your point for the 10 digits format requirement from OP. But humorously I say that not many of us will have a cash flow of 8 digits but large corporation or Government do go beyond 10 digits.