Round Fuction: Did I find a Bug???

[And]

I'm having this problem:



In a form (lets name it Form1) I have some fields:

Field1 with the value 1.000.000

Field2 with the value 0,19 (its a percent field so it displays 19,00%)

Field3 with the value 0.80 and

Field4 with the result of the operation Field1*Field2.



While this form is open another form is loaded. This form as a field with
the following control source:

=Round([Forms]![Form1]![Field4]*[Forms]![Form1]![Field3];2)

the result of this operation should be 1190000*0.8=952000 and since the
field is formatted as Standard with 2 decimal places it should display
952000,00 my problem is that it displays 952.000,01.



This even becomes weirder because I made some tests in visual basic and it
seems the problem is related with the data type. Next I present my tests and
why I say this:



First test:

Dim sgle As Double

sgle = Round(Form_Form1.Field4 * Form_Form1.Field3, 2)

MsgBox Round(sgle, 2)



Result: 952000,01 (Incorrect result)



Second test:

Dim sgle As Single

sgle = Round(Form_Form1.Field4 * Form_Form1.Field3, 2)

MsgBox Round(sgle, 2)



Result: 952000 (Correct result)





Did any one ever face the same problem? If so how did you solve it?



Tks in advance

And

 

[HennieJ]

Clarify please: Field4=Field1*Field2 should be 119000. And what´s your
decimal notation: point or comma? And please take into acount that when the
contents of the Fieldn´s are not exact, possibly truncated or rounded
themselves, the rounded result you see for the extra Field may be correct!
Try to display the fields with (much) more decimals, to see what is
happening.

HennieJ

 

[And]

Clarify please: Field4=Field1*Field2 should be 119000.

I'm sorry Field4=Field1*(1+Field2) = 1190000

And what´s your decimal notation: point or comma?

My decimal notation is comma (ex: 558.123.456,89 has two decimal places)

And please take into acount that when the contents of the Fieldn´s are not

exact, possibly truncated or rounded

themselves, the rounded result you see for the extra Field may be correct!

I guess this is not the case because most of the fields are exact and
because I already made some tests to find if this was the problem.

Try to display the fields with (much) more decimals, to see what is
happening.

With more decimal places the result is the same, i.e., if the control source
is

=Round([Forms]![Form1]![Field4]*[Forms]![Form1]![Field3];5) and I format the
fiel to standard with to decimal places the result is 952000,01228.

HennieJ

I'm having this problem:



In a form (lets name it Form1) I have some fields:

Field1 with the value 1.000.000

Field2 with the value 0,19 (its a percent field so it displays 19,00%)

Field3 with the value 0.80 and

Field4 with the result of the operation Field1*Field2.



While this form is open another form is loaded. This form as a field with
the following control source:

=Round([Forms]![Form1]![Field4]*[Forms]![Form1]![Field3];2)

the result of this operation should be 1190000*0.8=952000 and since the
field is formatted as Standard with 2 decimal places it should display
952000,00 my problem is that it displays 952.000,01.



This even becomes weirder because I made some tests in visual basic and it
seems the problem is related with the data type. Next I present my tests
and
why I say this:



First test:

Dim sgle As Double

sgle = Round(Form_Form1.Field4 * Form_Form1.Field3, 2)

MsgBox Round(sgle, 2)



Result: 952000,01 (Incorrect result)



Second test:

Dim sgle As Single

sgle = Round(Form_Form1.Field4 * Form_Form1.Field3, 2)

MsgBox Round(sgle, 2)



Result: 952000 (Correct result)





Did any one ever face the same problem? If so how did you solve it?



Tks in advance

And

 

[Allen Browne]

This is not a bug. It is a limitation of floating point numbers.

Firstly, I cannot repeat the result of your calcuation, even by forcing the
values to Single. I'm going to assume that these values are therefore the
result of some calculation that is not a precise value.

More importantly, floating point numbers such as Single or Double cannot
represent numbers to more than a few digits of precision - around 7 digits
for a single, or 15 for a double. The error in your example is in the 8th
digit, so I'm assuming the values are internally represented as single.

If you never need more than 4 decimal places, the simplest solution in
Access would be to use a field of type Currency. This is a fixed point
number (always 4 decimal places), not a floating point number, so it is not
subject to the limitations of floating point numbers.

 

[And]

This is not a bug. It is a limitation of floating point numbers.

Firstly, I cannot repeat the result of your calcuation, even by forcing the
values to Single. I'm going to assume that these values are therefore the
result of some calculation that is not a precise value.

No, all the values are precise values that I introduced in a table,
excluding the Field4 text field.

More importantly, floating point numbers such as Single or Double cannot
represent numbers to more than a few digits of precision - around 7 digits
for a single, or 15 for a double. The error in your example is in the 8th
digit, so I'm assuming the values are internally represented as single.

I changed all this fields to double type and the problem remains.

If you never need more than 4 decimal places, the simplest solution in
Access would be to use a field of type Currency. This is a fixed point
number (always 4 decimal places), not a floating point number, so it is not
subject to the limitations of floating point numbers.

And now for something completely strange. When I change the formula in the
second form " =Round([Forms]![Form1]![Field4]*[Forms]![Form1]![Field3];2)"
to "
=Round(CSng([Forms]![Form1]![Field4])*CSng([Forms]![Form1]![Field3]);2)" the
result is correct. I guess this is the opposite from what you are saying!

Tks for spending time with my question
And

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm having this problem:



In a form (lets name it Form1) I have some fields:

Field1 with the value 1.000.000

Field2 with the value 0,19 (its a percent field so it displays 19,00%)

Field3 with the value 0.80 and

Field4 with the result of the operation Field1*Field2.



While this form is open another form is loaded. This form as a field with
the following control source:

=Round([Forms]![Form1]![Field4]*[Forms]![Form1]![Field3];2)

the result of this operation should be 1190000*0.8=952000 and since the
field is formatted as Standard with 2 decimal places it should display
952000,00 my problem is that it displays 952.000,01.



This even becomes weirder because I made some tests in visual basic and it
seems the problem is related with the data type. Next I present my tests
and
why I say this:



First test:

Dim sgle As Double

sgle = Round(Form_Form1.Field4 * Form_Form1.Field3, 2)

MsgBox Round(sgle, 2)



Result: 952000,01 (Incorrect result)



Second test:

Dim sgle As Single

sgle = Round(Form_Form1.Field4 * Form_Form1.Field3, 2)

MsgBox Round(sgle, 2)



Result: 952000 (Correct result)





Did any one ever face the same problem? If so how did you solve it?



Tks in advance

And

 

[M.L. Sco Scofield]

There is some great info on rounding in the Tips column of the January 2005
Access ... Advisor magazine.

It talks about both the different kinds of rounding and floating point
errors.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Allen Browne]

Since I cannot reproduce this, I'm wondering what the difference is between
your system and mine. Clearly there are some differences in regional
settings, but I doubt that is the cause of the issue.

What version of Access is this? And what service pack? Locate the file
msaccess.exe (typically in c:\program files\microsoft office\office).
Right-click, and choose the Version tab. What is the full version number?

Open the Immediate window (Ctrl+G), and enter:
? Format(1190000 * 0.8, "#0.00")
Does this yield the correct result?

The fact that conversion to single avoids the problem in this particular
case still suggests that the issue is to do with floating point inaccuracies

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This is not a bug. It is a limitation of floating point numbers.

Firstly, I cannot repeat the result of your calcuation, even by forcing the
values to Single. I'm going to assume that these values are therefore the
result of some calculation that is not a precise value.

No, all the values are precise values that I introduced in a table,
excluding the Field4 text field.

More importantly, floating point numbers such as Single or Double cannot
represent numbers to more than a few digits of precision - around 7
digits
for a single, or 15 for a double. The error in your example is in the 8th
digit, so I'm assuming the values are internally represented as single.

I changed all this fields to double type and the problem remains.

If you never need more than 4 decimal places, the simplest solution in
Access would be to use a field of type Currency. This is a fixed point
number (always 4 decimal places), not a floating point number, so it is not
subject to the limitations of floating point numbers.

And now for something completely strange. When I change the formula in the
second form " =Round([Forms]![Form1]![Field4]*[Forms]![Form1]![Field3];2)"
to "
=Round(CSng([Forms]![Form1]![Field4])*CSng([Forms]![Form1]![Field3]);2)"
the
result is correct. I guess this is the opposite from what you are saying!

Tks for spending time with my question
And

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm having this problem:



In a form (lets name it Form1) I have some fields:

Field1 with the value 1.000.000

Field2 with the value 0,19 (its a percent field so it displays 19,00%)

Field3 with the value 0.80 and

Field4 with the result of the operation Field1*Field2.



While this form is open another form is loaded. This form as a field with
the following control source:

=Round([Forms]![Form1]![Field4]*[Forms]![Form1]![Field3];2)

the result of this operation should be 1190000*0.8=952000 and since the
field is formatted as Standard with 2 decimal places it should display
952000,00 my problem is that it displays 952.000,01.



This even becomes weirder because I made some tests in visual basic and it
seems the problem is related with the data type. Next I present my
tests
and
why I say this:



First test:

Dim sgle As Double

sgle = Round(Form_Form1.Field4 * Form_Form1.Field3, 2)

MsgBox Round(sgle, 2)



Result: 952000,01 (Incorrect result)



Second test:

Dim sgle As Single

sgle = Round(Form_Form1.Field4 * Form_Form1.Field3, 2)

MsgBox Round(sgle, 2)



Result: 952000 (Correct result)





Did any one ever face the same problem? If so how did you solve it?



Tks in advance

And