FAQ request: formatting numbers for currency

[smandoli9]

What about a FAQ on formatting numbers for currency on forms? I've lost some hair on this one. If anyone has a good web site or even a book to pick up, your reply is valued.

My project is sales/mfg. oriented. In forms, I hate the way 1 to 6 decimal digits pop into view when the control as the focus. To solve this, I built FormatCurrency() into lower level forms, then discovered that the result seems to be a string, and then developed widespread failures in queries -- had to back fully out of that one.

This FAQ I'm imagining would start with the news that currency as a data type is unrelated to currency as a format. This insight has been late coming to me.

Hmm ... I don't think the whole rounding issue affects me (as I'm not converting yen to yeuros, or amortizing the mortgage on royalties, etc.). So what about using Fixed format (two decimal places) at the table and query level, and Currency formatting at the form level? Sorry for brainstorming on my own post ... but that is perhaps my answer, and perhaps you have some feedback on that.



[purple]_______________________________
[sub]Never confuse movement with action -- E. Hemingway [/sub][/purple]

 

[GDGarth]

I just change the text box format to Currency, and it displays the value as currency, rounding to two digits. This doesn't change the underlying value, so rounding isn't a factor.

 

[smandoli9]

GDGarth,

Thanks for reply. For example: my selected item costs $6,745.10. However in my table it registers as $6,745.1015. (I imported this data from Excel; don't expect to have 4 decimal places from that source, but there they are. It's an Access Currency thing, right?)

On most forms I want the dollars, no cents, so I limit decimals to 0 for that text box. So far so good. But when that text box receives the focus, the value changes from $6,745 to $6,745.1015.

If I'm displaying calculated fields -- like applying a percentage -- I would really like 0 (or maybe in some cases 2) decimal places in the text box. But upon putting focus on the text box, decimal figures 10 deep can appear.

Idea: I don't why the Excel info is coming in with 4 decimal places, but I do find that in the table I can delete the number to 2 decimal places. With an update query, I can truncate the figures.

Am looking however for an approach that also deals with the calculated fields issue. The comfort level of the end user is my concern. I unnerves me when I click on the field and the digits explode -- and I'm the developer!

[purple]_______________________________
[sub]Never confuse movement with action -- E. Hemingway [/sub][/purple]