Number or Currency Formats in Access – Which to Use?

Topic

New Reply

I am using MS Access 2007 on Windows 7.

When I took classes in Access about a decade ago, my teacher told us that if numbers we enter into our database are going to be used to do math, we should always format them as currency. She had some explanation for this which I no longer recall but which made sense at the time. I have followed her advice, and when the numbers I’m using aren’t ACTUALLY currency (they usually aren’t), I have simply changed the decimal and leading symbol settings on the field so they display simply as numbers, not currency.

First question: Was this accurate information (that numbers to be used for math should be formatted as currency)?
If so, then
Second question: How do I get a sum field in a calculate query to display WITHOUT the currency symbol? I’ve never had this problem before Access 2007 but now, even though the numbers in my table and in my regular queries display without the symbol, once total them in a Totals query, there is a currency symbol (in this case a dollar sign) in front of them and two decimal places appear, whether or not there were decimal places in the original data.

Thank you for any tips.

Reply | Quote

Replies

I guess this is why that recommendation was made. Whether you should follow it or not, really depends on what you are going to do with the numbers. As the example states, the results are accurate up to the 14th decimal place.
Rounding the expression in the example, you still get a zero: ? Round(100.99 – 50.45 – 50.54,13) would get you zero. So, as I said, it really depends on what you plan to do with the numbers.

My most recent app dealt with performance evaluation and rankings. A specific request was that all results should be presented with 3 decimal places. All I did was to round any relevant calculation to make sure only 3 decimal places were really taken into account. I used Double numbers and had no issues with it.

Reply | Quote

As I understand it the Currency datatype uses a special set or routines called Binary Coded Decimal or BCD.
Since a Binary number can not represent TENTHS as in 1/10 or 1/100 precisely you will invariably get rounding errors when working with Dollars and Cents. The BCD routines code the Binary values in a special way to avoid this problem. There is a performance penalty but in most cases you probably won’t notice it and the accuracy is what really counts anyway.

Back in the bad old days on my Osborne 1 there was a special BASIC language “Business Basic” which had the BCD routines that were not in MS Basic.:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

So, it looks like there is not really any reason to use “currency” format if I’m not actually using dollars and cents. Is that right? I don’t do any complicated math with the numbers, just addition and subtraction.

Reply | Quote

So, it looks like there is not really any reason to use “currency” format if I’m not actually using dollars and cents. Is that right? I don’t do any complicated math with the numbers, just addition and subtraction.

The Accountant in me says use them when absolute accuracy of decimal places is required…I remember trying to find that .01 when I was out of balance at the end of the period! Then there was the famous Lotus 123 lawsuit where they were off by .25 Million! :o:

In the end only you know how important exact accuracy to your application.:rolleyes:
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

So, it looks like there is not really any reason to use “currency” format if I’m not actually using dollars and cents. Is that right? I don’t do any complicated math with the numbers, just addition and subtraction.

The problem with using single and doubles is that Access doesn’t actually directly store the number you enter, it stores the exponential that approximates the number you enter. Granted it is a very close approximation, but it can lead to problems.

for example, if you stored the result of a calculation into a single (or even if you allowed the user to enter a number on-screen), and that calculation produced a number like 14.9999999999, it would probably be presented as 15 on-screen or on a report. But if you tried to run a selection query looking for values >=15, it would not appear!

I mostly use currency datatypes whenever I have to store decimals. In the above situation, that 14.9999999 would have been stored as 15.0000; so it will appear as expected when I run a selection query.

Reply | Quote

I never actually felt that need. Using rounding in proper places, for normal usage, allows you to use the other number types without any issues.

Reply | Quote