Too many digits after decimal.

[bonniebelle200]

I have a query that is pulling from a single table of expenses the sum of expenses for each quarter. I’m using only three fields: the quarter, expense type and amount paid. I’m using “Qtr: DatePart("q",[PostDate]) “ to obtain the quarters, grouping by quarter and then by expense type. However, the record returned for the 4th quarter shows this result: “3.0026” It should not have four digits after the decimal.

Results for the other three quarters in the same query return correctly with two digits after the decimal point. The query below was suggested to me in order to make the sum returned have only two digits after the decimal:
AmtPaid: Sum((SELECT FORMAT(PymntAMOUNT, 2) FROM tbl_expenses))

The result of this was a curious information box that said: “At most one record can be returned by this subquery.” The query returned nothing. So, I’m at a loss. I have checked the formatting in both the table and the query itself and the format is “Standard” for both. So, I can’t figure out what is producing this 4 digit problem, and why it is only in one record while all the others have two digits after the decimal. Can someone help me resolve this?

 

[Cronk]

I don't know why you are getting 4 decimals displayed. The field is defined as Currency?

The format function returns a string, so you can't add. In any case, Format(n,2) always returns the character 2.

Use the Round function to give the required number of decimal places.

However, currencies are stored as 4 decimal amounts for accuracy and if you were the use
Sum(Round(Nz([PymntAMOUNT]),2))
you might get rounding errors.

Try
select top 1 Round((SELECT Sum(Nz([PymntAMOUNT])) AS AmountPaid
from tbl_expenses),2) AS AmountPaid from tbl_expenses

 

[RainLover]

Suggest you remove all the formatting you have now or create a Test Form.

Go to the Table and set your data type as follows.

Format - Currency
Decimal Places - 2

That's it. It should work as required as far as the Display is concerned. However what is stored is 4 Decimal Places.

 

[bonniebelle200]

Thanks Cronk,
The second expression did not work. Access didn’t like it.
The first expression worked: AmtPaid: Sum(Round(Nz([PymntAMOUNT]),2))
Returned 3.02 with only 2 digits and none "hiding" in the database field as before. I guess it remains to be seen whether it causes rounding errors in the future.

 

[Brianwarnock]

How does 3.0026 become 3.02 and be correct? Am I missing something?

Hi Rain its great when you don't even get a nod to your posts isn't it?

Brian

 

[RainLover]

How does 3.0026 become 3.02 and be correct? Am I missing something?

Hi Rain its great when you don't even get a nod to your posts isn't it?

Brian

Its all right Brian. I know what rejection is like. I just have another drink and I am fine. Besides I have over 10,000 posts (In other forums)and know what to expect.

But check this calculation out.

10/3 = 3.33

3.33 * 3 = 9.99

So if the OP is going to do a lot of calculations the errors are just going to grow.

However I don't know what the scope is that the OP is working with so what he is using may be right for him. I would prefer to use what I suggested, but that is simply my preference.

Cronk. This is not a critism of you..

 

[nanscombe]

It's Hard To Be Humble

Definitely +1 for:
Field type of currency
Do the Sum() without any change to the field values.
Amend the query to display the output as Currency format

Even if the original table just had a number field you could still format the output field as currency in the query designer.

Let Access take care of any rounding.

 

[RainLover]

It's Hard To Be Humble

Definitely +1 for:
Field type of currency
Do the Sum() without any change to the field values.
Amend the query to display the output as Currency format

Even if the original table just had a number field you could still format the output field as currency in the query designer.

Let Access take care of any rounding.

Nigel

Did you find something wrong with my suggestion or is this just an alternative.

Yours in all Humbleness.

 

[nanscombe]

Definitely +1 for:

Another vote for ..

Field type of currency

Agreeing with your suggestion.

Do the Sum() without any change to the field values.

If you are using currency values surely you shouldn't need to use Round().

Amend the query to display the output as Currency format

A way of ensuring the output displays currency format even if the original fields were in some other numeric format.

What's so alternative about that?



As for the humble bit ...

Hi Rain its great when you don't even get a nod to your posts isn't it?

Its all right Brian. I know what rejection is like. I just have another drink and I am fine. Besides I have over 10,000 posts (In other forums)and know what to expect.

Just joining in the humour.

 

[RainLover]

Nigel

Thanks for the clarification. When I first read your original post it had me thinking down a different path.

Just wanted to be sure I understood correctly.

 

[Cronk]

Rainlover,

Re #6 I didn't take it as criticism.

I had assumed it was currency type values being summed, rather than Double.

 

[bonniebelle200]

Brian, I understand your confusion - I guess I didn't explain the issue in full. But the $3.02 figure returned is exactly what the calculation was supposed to be (I checked it manually). My confusion is how did the database come up with 3.0026 in the first place - a bug? I didn't even understand how it came out correctly and it didn't affect the other records at all. Remember all records returned correct calculations except the last one.

My son had advised me to use the "Round" function but as several of you suggested, the field was already set to currency with 2 decimal places. So, I didn't think I needed to Round a currency value. Hence, the reason for my post. But, when so many others suggested using the Round function I gave up and tried it anyway, and it worked. It blew my mind - it was the weirdest thing I've ever seen and I wish someone could explain it to me. I'm waiting to see if it causes an issue.

My theory is this: Since none of the other figures were affected, I believe it has something to do with Access' calculations when you enter some numbers as a percent of another number. In Excel, if two numbers are the same (like .26 and .26) each calculated x 10% would be returned as $.03 each = total $.06. The finished total calculates the percentage on the two numbers after adding them together ($.52), and returns the percentage as $.05, one penny less than when they're calculated separately. Since the percentages are being calculated by Access, I figured it must have dropped the extra penny like Excel does? So, the $3.0026 figure was already rounded to $3.00. But with tithing, my calculations have to be exact - each tithe paid must be calculated without dropping the extra penny when the numbers are added together (it's like stealing a penny each time if you don't), Oh! My Lord - those pesky penny errors!!

Thank you to everyone who replied to my post, and if you don't mind, please let me know what you think about my theory.

 

[Brianwarnock]

It is nothing to do with software although it may exacerbate the problem, it is basic maths that you should not sum the percentages , or any rounding methodologies , of details to calculate the final total but always work with the raw data, your example points out why.

At a place I worked once I had to explain that a total of the percentages may not add upto 100% , and they insisted that I fiddle the figures , thus making one of the details figures incorrect. They never understood, perhaps I'm a poor teacher.

Brian

 

[RainLover]

Try doing the Math on a piece of paper using the same rules you gave Access.

Namely, Decimal Places and Rounding.

You will get exactly the same answer.

As a matter of related interest. My phone bill comes with each figure calculated and shown to 3 decimal places. The total is rounded to two places. The more decimal places you use the more accurate the result.

 

[nanscombe]

Here's what Microsoft says about the currency field type.

Choosing between a number and a currency field

Microsoft Access provides two field data types to store data containing numeric values: Number and Currency.

Use a Number field to store numeric data to be used for mathematical calculations, except calculations that involve money or that require a high degree of accuracy. The kind and size of numeric values that can be stored in a Number field is controlled by setting the FieldSize property. For example, the Byte field size will only store whole numbers (no decimal values) from 0 to 255 and occupies 1 byte of disk space.

Use a Currency field to prevent rounding off during calculations. A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right. A Currency field occupies 8 bytes of disk space.

Number and Currency fields provide predefined display formats, or you can create a custom format.

Just because it only shows 2 decimal places doesn't mean it has to calculate and store the numbers as such.

 

[bonniebelle200]

You "guys" are the BOMB! I wish I'd found this site years ago. Thank you all for being so informative!! YOU ROCK!

 

[bonniebelle200]

Brian, I will make a note of your comment to "...not sum the percentages." In my mind I guess I'd figured it out because my ultimate conclusion was not to sum the percentages, but I'd never heard it spoken as a general rule. I've been doing it all the time. But never again, thanks to this problem and all the discussion.
Thanks again.