balance showing strange results (1 Viewer)

[rainbows]

hi , i am confused as to why i get strange numbers when i put numbers after the .dot for example
below you can see the quantity reqd is 150 .439, i have had 2 deliveries booked in 103.439 and 47.00
which now equals the reqd qty the field in yellow should show 0 and the conditioning format is green
as you can see i have a strange result


if i click on the total qty recieved you will see i have got to many numbers again i dont know why see screen shot 2
in screen shot no 3 if i use whole numbers it is correct

yet screen shot 4 is not whole numbers but is ok

confused

 

[theDBguy]

If you're dealing with floating numbers, you could be seeing scientific notation. If that's the issue, try adding a Format to your textboxes to limit the number of digits displayed.

 

[MajP]

Use a decimal data type too, or round your results. In the first example your rejeceted quantity is
.00000076 so it is basically 0.

 

[The_Doc_Man]

You are dealing with decimal fractions which can become a problem on a binary machine.

First, remember from your grade-school days that 1/3 is a repeating decimal fraction of 0.33333...33333....ad infinitum. So if you add 1/3+1/3+1/3 you can clearly say it is 3/3 and make it 1. But adding 0.33333... + 0.33333... + 0.33333... comes out to 0.99999.... which isn't QUITE 1.

Now, on a binary machine with floating point hardware, the problem with a decimal fraction - let's start with 1/10 - is factoring it. We know 1/10 is actually 1/2 * 1/5, so that looks like a good start. The fraction 1/2 is not a problem on a binary machine. BUT that stinker 1/5 isn't so nice. As it turns out, the binary fraction for 1/5 is something like 0.0011001100110011....00110011....ad infinitum. Another repeating-digit fraction - which oddly enough, if converted to hexadecimal, is ALSO 0x0.33333...33333...ad infinitum. Pesky darned 3's.

Anyway, the point is that when dealing with floating point numbers, you want to understand that fractions stored this way CANNOT accurately represent numbers that repeat ad infinitum. At some point you reach a truncation or round-off error, one of the two, because you have run out of bits to represent your fractional number.

The suggestions you have gotten avoid that problem by either truncating the display of extra digits OR by using a scaled integer rather than a floating point number to represent fractional quantities. Depending on the range of numbers you might expect to see, perhaps you should also look into using CURRENCY data type, which is a scaled integer allowing four decimal places. A normal LONG integer can only handle 4 billion (unsigned) or 2 billion (signed), but you show three decimal places so that means a scaled-by-3 LONG can only handle 2 or 4 MILLION. CURRENCY can easily go to a much larger number and still not get caught in fractional situations.

 

[rainbows]

i am not sure if this can be correct or not but it seems to be working although i did change the qty just to check before the feild size was single

 

[MajP]

Why are you still using a Double instead of a decimal? I imagine you are limited to two decimal places max (scale). I do not know if you set the decimal places on a double if that is just formatting and not how the data is stored. May want to review. Or just simply round your answer to appropriate decimal places.

 

[The_Doc_Man]

MajP's concern - and mine - is that if all you did was to change SINGLE to DOUBLE and set formatting to 3 decimal places, you get more bits to work with, but all you did was kick the can down the road. Eventually as you walk down that road, you will come back to the same can, now a little bit scuffed. The good news is that you get over twice as many bits to represent your fractions. The bad news is that errors can still creep in on DOUBLE numbers - it just takes longer. It is an inherent limitation in INTEGER vs. REAL number representation.

 

[rainbows]

ok . i dont want to get into problems later so i am not sure what i should change them fields to . should it be decimal ?
can you round formulars ie =[qtyrecd]-[qtyreqd]-[rejected]
steve

 

[arnelgp]

you can change the Field to Decimal or you can Create a Function in a Module to change them
to decimals before the Subtraction:

Public Function fnDec(byval n As Double) As Double
fnDec = CDec(n)
End Function

CDec(qtyrecd])-Cdec([qtyreqd])-CDec([rejected])

 

[Pat Hartman]

Formatting a number in a table or a control does NOT in any way affect the stored value. All it does is hide the actual value from you. You still get floating point errors and you still get totals that don't equal the parts. There are two ways to solve the problem but #1 is better than #2.

1. Use a scaled integer like Decimal or Currency. I use Currency because I almost never have to deal with decimals longer than four digits which is the limit for Currency. Currency is also a format as well as a data type. Do not confuse the two. A scaled integer uses an asumed decimal point. It is always 4 digits to the left with Currency. So 123.456 is stored as 1234560. The trailing zero gets the decimal in the correct location. 12.34 = 123400. The math is always integer math rather than floating point and the result is rounded to four decimal places. If you need more than four decimal places, you can use Decimal or even Long Integer but if you use an Integer data type, you have to do the scaling.
2. Use Single or Double but in EVERY instance, round the value before storing it. That doesn't solve the problem of 1/3's though. You still end up with .3333 + .3333 + .3333

 

[cheekybuddha]

Currency is also a format as well as a data type. Do not confuse the two.

This needs to be pointed out more often.