Query shows more digits after comma than there is in a table

[Doorman]

This is really something, has never happened to me in Access 2003. Now among some other things in 2007 this really bothers me:

There is a table with number which have 2 digits after decimal point.
Query calculates the sum of the sum of this column. Somehow I am getting more digits.

Table input:

Table properties:

Query:

SELECT Sum(Appended.sum) AS sssum FROM Appended;

Results of a query

Why it is not 15,15 as a result?
Please advise.

 

[jal]

I don't know about Access, but over at CsharpFriends.Com a guru named Vulpes made the following comment about the VB.Net and C#.Net programming languages, when someone there made a similar complaint. He said:

"Inaccuracies can happen with floats because floating point numbers (i.e. double and float) do not in general have an exact bnary representation within the machine. If I were you, I'd use the decimal type for exact work, as it doesn't suffer from these problems (it's correct to about 28 digits). The only drawbacks to using decimal are that it is much slower if you have a large number of calculations and requires more memory (16 bytes) than double (8 bytes) or float (4 bytes).However, in most applications and on today's hardware, these drawbacks do not matter much in practice."

Maybe you could try double or decimal, or use the Round or Format functions. Round is pretty straightforward, but I can give you some tips on Format if you need some.

 

[huilmn]

In Design View of the Query, right click in the field, In Properties choose Fixed from the Format list, and 2 from Decimal Places. That should do it.

 

[Doorman]

jal, thanks, for a reply
I have tried Double - same story. The thing is that I indicate already that it should be 2 decimals (2nd screen shot).

Of course I could do SELECT Format(Sum([sum]),"0.00") AS sssum
FROM Appended;

But I do not think it a good solution because it would mean that I need to do it for every Number column in every query to be sure that it displays what there truly is.

Just wanted to know how to know when it is happening and how to avoid it.
Any suggestions?

 

[raskew]

Hi -

Try Currency as the data type.

Bob

 

[Doorman]

Thanks guys for your responses.

@Bob, that adds a currency sign. Don't want to see it.

Strangely it is happening just for one sum (aggregated month data). Other sums (other months) through month were fine.
I did it with Properties choose Fixed from the Format list. (thx to huilmn)
Well, will just keep in mind that it is should be done sometimes.

 

[jal]

Thanks guys for your responses.

@Bob, that adds a currency sign. Don't want to see it.

Strangely it is happening just for one sum (aggregated month data). Other sums (other months) through month were fine.
I did it with Properties choose Fixed from the Format list. (thx to huilmn)
Well, will just keep in mind that it is should be done sometimes.

You said you tried Double - did you also try decimal?

 

[jal]

Oh, I just tried decimal - looks like it truncated all my previously typed in decimal numbers. That's odd.

 

[jal]

Ok, if I set the 'scale' field it doesn't truncate.

 

[Doorman]

Oh, I see..., I tried but didn't use decimal, because it truncated the entry. Yep, 'scale' solves the issue.

 

[Pat Hartman]

Don't confuse the Currency data type (which specifies how a number is stored) with the Currency format (which specifies how a number is displayed).

 

[antoka05]

Try to use 'round' function:

SELECT Round(Sum(Appended.sum),2) AS sssum FROM Appended;

Regards,
Antonio