Rounding off decimals places error 1

[dhanus]

I am trying to use a combination of str() and left() functions to display results of calculations in my reports without the system rounding the results but I get funny results.

My problem is I may have for example 0.01042 and 0.00048 as detail lines. They total up as 0.01090 but I want to display them in 3 decimal places. So I get 0.010 and 0.000
in the detail lines but the total line displays 0.011. It gets worse when there are alot of records.

The str() and left() functions does not seem to work with calculated fields.

Any help will be much appreciated.

 

[JoanneM]

hello

on the text box with the numbers on your report go to properties

set the format property to standard
and decimal places property to 3

this should resolve your display issues without touching the data

regards

jo

 

[dhanus]

Hi Jo

That's precisely my problem, using the format property which rounds up my numbers which do not tally with my group total.

As appears in my report e.g.

Detail line 1 0.010
Detail line 2 0.000
Group total 0.011

because the actual calculated field results are
0.01042
0.00048
which total up to
0.01090

So in actual fact my display should be without the rounding up :

Detail line 1 0.010
Detail line 2 0.000
Group total 0.010

 

[StellaIndigo]

Try this,

StrValue: Left(CStr([value]),InStr(1,CStr([value]),".")+3)

Return a 3 decimal place string for printing

while

DblValue: CDbl(Left(CStr([value]),InStr(1,CStr([value]),".")+3))

Returns the string converted back to a double for you to sum at the end of the report.

This is truncating to 3 decimal places not rounding.

Stella

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[dhanus]

Hi Stella

The formula gives me wierd answers which is consistent with the function that I tried to write myself :

A B A-B Required Your formula
Actual result result

36.4 36.45 -0.05 -0.050 -5.000
36.37 36.4 -0.03 -0.030 -3.000
36.24 36.26 -0.02 0.020 -0.019
36.29 36.18 0.11 0.110 0.109
36.36 36.31 0.05 0.050 4.999
36.22 36.29 -0.07 -0.070 -7.00

Did I do something wrong? I entered the formula as :
Left(CStr([A]-),InStr(1,CStr([A]-),".")+3)

Thanks

 

[StellaIndigo]

OK

I tested this with values in a table and it worked ok. Problem here is that the conversion to a String from the expression you include "CStr([A]-)" converts to a Scientific format (e.g. -7.00000000000003E-02) hence your incorrect values.

Try this different method,

Format$(CStr(CInt(((CDbl([value])-CDbl([value2]))*1000))/1000),"0.000")

This also ensures the string is formatted to 3 decimal places.



Regards
Stella

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[dhanus]

Hi Stella

Gee.. It worked !

Where can I find the explanations of the functions CStr(), CDbl() etc?

So, if I want my results in 2 decimals, I will have to divide the result by 100 instead of 1000. I don't quite get the logic.

Thanks !

 

[StellaIndigo]

No problem.

CStr, CDbl etc. are type casting functions. You may not need the CDbl's in the middle of the expression but Access and VBA are crap for performing math. I put them in expressions to make sure I get the right result and to make it clear when looking at expressions what I'm doing.

All I'm doing in the expression is multiply by by 1000 and then dropping the fractional part then divide down to get the 3 decimals.

Yes if you *100 then /100 you'll get 2 decimal places. Also change the formatting string to "0.00".

Hope this helps



Regards
Stella

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[dhanus]

Thanks, Stella for the explanation.