SUM and decimal places 1

[sblanche]

I have a table with 10 fields (FieldA, Field B, etc) where we enter numbers using 2 decimal places (.02, .75, .08, etc). In the table setup the "Format" is "Standard" and the "Decimal Place" is 2. When I do a query using "SUM" in the "Total" field, I get numbers such as 7.259941225511. I only want 2 decimal places. In the query I have tried changing the properties for each field to 2 decimal places but that doesn't work. Any help would be appreciated.
Thanks,
SLB

 

[SkipVought]

Hi,

You could use the Format function (Format(MyExperssion, "#.00")), however that does not change the underlying value which will STILL be 7.259941225511.

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]

 

[Golom]

The format property of a field controls how the field is displayed ... NOT how it is stored. You can get your 2-decimal representation with

Val(Format([SomeField],"0.00"))

OR

Round([SomeField],2)

 

[Golom]

Sorry Skip ... too slow again!

 

[sblanche]

Wow --what a fast response. Sorry for the basic question but where do I put Round([SomeField],2). I use the Query Wizzard to create the query so I'm not sure where it goes. When I said I use "SUM" it's just an option you can pick for the Total field. Also, I just noticed something about the totals. When it rounds I need to make sure it does it correctly. What I mean is this. When I manally add a field it totals 7.04. When the "SUM" totals I get 7.03999996.

Question: Skip--When you said "does not change the underlying value which will STILL be 7.259941225511" why is the underlying value so long. Doesn't .01 +.02 + .04 equal .07? I just put those numbers in a test run and got 0.0699999984353781. Geeze. I'm confused. Guess 1+1 does not always = 2.

SLB

 

[PHV]

Guess 1+1 does not always = 2
Not necessarly true with floating point numbers as they are approximation only.

 

[SkipVought]

Check out

http://stevehollasch.com/cgindex/coding/ieeefloat.html.

If your eyes don't glaze over, at least realize that Single and Double number types are good for APPROXIMATIONS.

If you need better precision, you'll need to use INTEGER ARITHMETIC techniques.

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]