Incorrect sum

[Guest]

Access2003

I'm trying to sum several fields but I'm getting wierd results. I'm using
the following format in an unbound field titled "Total":

=(Nz([FieldOne],0)+(Nz([FieldTwo],0))

I'm dealing with percents, so I've also added an AfterUpdate event to my
bound fields:

Me.FieldOne.Value = Me.FieldOne.Value / 100

Now, when I type in "1" into FieldOne (or in FieldTwo) the Sum comes back as
"00999999977648258" in my unbound Total field.

What gives?!

TIA

Aaron G
Philadelphia, PA

 

[Guest]

When you use the expression Nz([FieldOne],0) it then sees your field as text.
When I tested your expression below with a 1 in FieldOne and 0 in FieldTwo
the result was 10.

Try using Nz([FieldOne])+0 if you want the result to be zero.

Hope this helps.

 

[Guest]

Jackie,

The bound fields in the table are set up in the tables as DataType "Number"
and size is "Single". The same bound fields on the form are set up with the
Format "Percent".

I do not want a zero value (unless all fields are null). I want a "1" when
I type in "1". Instead I'm getting ".00999999977648258".

All I want is "1" to equal "1" when entered (and displayed) as a percent.

Please advise.

Aaron G
Philadelphia, PA

When you use the expression Nz([FieldOne],0) it then sees your field as text.
When I tested your expression below with a 1 in FieldOne and 0 in FieldTwo
the result was 10.

Try using Nz([FieldOne])+0 if you want the result to be zero.

Hope this helps.

Access2003

I'm trying to sum several fields but I'm getting wierd results. I'm using
the following format in an unbound field titled "Total":

=(Nz([FieldOne],0)+(Nz([FieldTwo],0))

I'm dealing with percents, so I've also added an AfterUpdate event to my
bound fields:

Me.FieldOne.Value = Me.FieldOne.Value / 100

Now, when I type in "1" into FieldOne (or in FieldTwo) the Sum comes back as
"00999999977648258" in my unbound Total field.

What gives?!

TIA

Aaron G
Philadelphia, PA

 

[Graham Mandeno]

Hi Aaron

What is the format property of the textbox? You should set it to "Percent"
and the DecimalPlaces to 0, 1 or 2, or whatever is appropriate.

Remember that computers don't "think" in decimal (base 10) but in binary
(base 2). This means that an arithmetic operation such as 1/100 will not
necessarily give *exactly* 0.01, because 0.01 is not a number that can be
stored exactly in binary. So the binary result of the division is returning
something that approximates to the decimal number 0.00999999977648258.

Setting the DecimalPlaces property to 2 will force it to be rounded to 0.01
for formatting purposes, and setting the format to "Percent" will have it
display 1.00%

 

[Guest]

It's a somewhat well known problem. Computer sometimes have problem with
floating point math. Notice I said computers and not Access. In the Immediate
window you can do this simple math and see the same problem:

Debug.Print 3.1-3.11

Calculator back in the days of Windows 3 and 3.1 would do the same thing.
Enough with the history. What to do about it. You could use the Round()
function and it should return the nearest integer. You could also convert the
number to currency by using the CCur() which does a much better job of
floating point math.

Also the number is you example is much closer to 0 than 1. It's almost .01
so maybe it's more of a case of your expectations not matching the data.


--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jackie,

The bound fields in the table are set up in the tables as DataType "Number"
and size is "Single". The same bound fields on the form are set up with the
Format "Percent".

I do not want a zero value (unless all fields are null). I want a "1" when
I type in "1". Instead I'm getting ".00999999977648258".

All I want is "1" to equal "1" when entered (and displayed) as a percent.

Please advise.

Aaron G
Philadelphia, PA

When you use the expression Nz([FieldOne],0) it then sees your field as text.
When I tested your expression below with a 1 in FieldOne and 0 in FieldTwo
the result was 10.

Try using Nz([FieldOne])+0 if you want the result to be zero.

Hope this helps.

Access2003

I'm trying to sum several fields but I'm getting wierd results. I'm using
the following format in an unbound field titled "Total":

=(Nz([FieldOne],0)+(Nz([FieldTwo],0))

I'm dealing with percents, so I've also added an AfterUpdate event to my
bound fields:

Me.FieldOne.Value = Me.FieldOne.Value / 100

Now, when I type in "1" into FieldOne (or in FieldTwo) the Sum comes back as
"00999999977648258" in my unbound Total field.

What gives?!

TIA

Aaron G
Philadelphia, PA

 

[Guest]

Jerry,

I always love relavant history lessons. Thanks for the lowdown. I'll use
the round function to solve the issue.

By the way, fantastic quote on your signature.

Thanks again for your help.

Aaron G
Philadelphia, PA

It's a somewhat well known problem. Computer sometimes have problem with
floating point math. Notice I said computers and not Access. In the Immediate
window you can do this simple math and see the same problem:

Debug.Print 3.1-3.11

Calculator back in the days of Windows 3 and 3.1 would do the same thing.
Enough with the history. What to do about it. You could use the Round()
function and it should return the nearest integer. You could also convert the
number to currency by using the CCur() which does a much better job of
floating point math.

Also the number is you example is much closer to 0 than 1. It's almost .01
so maybe it's more of a case of your expectations not matching the data.


--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jackie,

The bound fields in the table are set up in the tables as DataType "Number"
and size is "Single". The same bound fields on the form are set up with the
Format "Percent".

I do not want a zero value (unless all fields are null). I want a "1" when
I type in "1". Instead I'm getting ".00999999977648258".

All I want is "1" to equal "1" when entered (and displayed) as a percent.

Please advise.

Aaron G
Philadelphia, PA

When you use the expression Nz([FieldOne],0) it then sees your field as text.
When I tested your expression below with a 1 in FieldOne and 0 in FieldTwo
the result was 10.

Try using Nz([FieldOne])+0 if you want the result to be zero.

Hope this helps.

:

Access2003

I'm trying to sum several fields but I'm getting wierd results. I'm using
the following format in an unbound field titled "Total":

=(Nz([FieldOne],0)+(Nz([FieldTwo],0))

I'm dealing with percents, so I've also added an AfterUpdate event to my
bound fields:

Me.FieldOne.Value = Me.FieldOne.Value / 100

Now, when I type in "1" into FieldOne (or in FieldTwo) the Sum comes back as
"00999999977648258" in my unbound Total field.

What gives?!

TIA

Aaron G
Philadelphia, PA

 

[Guest]

Graham,

Thanks for the info. I'm going to use the round function to ensure I'm
dealing with the complete numbers as mentioned above by Jerry. I appreciate
your response though!

Thanks again.

Aaron G
Philadelphia, PA

Hi Aaron

What is the format property of the textbox? You should set it to "Percent"
and the DecimalPlaces to 0, 1 or 2, or whatever is appropriate.

Remember that computers don't "think" in decimal (base 10) but in binary
(base 2). This means that an arithmetic operation such as 1/100 will not
necessarily give *exactly* 0.01, because 0.01 is not a number that can be
stored exactly in binary. So the binary result of the division is returning
something that approximates to the decimal number 0.00999999977648258.

Setting the DecimalPlaces property to 2 will force it to be rounded to 0.01
for formatting purposes, and setting the format to "Percent" will have it
display 1.00%
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Access2003

I'm trying to sum several fields but I'm getting wierd results. I'm using
the following format in an unbound field titled "Total":

=(Nz([FieldOne],0)+(Nz([FieldTwo],0))

I'm dealing with percents, so I've also added an AfterUpdate event to my
bound fields:

Me.FieldOne.Value = Me.FieldOne.Value / 100

Now, when I type in "1" into FieldOne (or in FieldTwo) the Sum comes back
as
"00999999977648258" in my unbound Total field.

What gives?!

TIA

Aaron G
Philadelphia, PA