Some values have extra decimals in query (1 Viewer)

[zebrafoot]

Good morning.

I'm looking to provide totals in a query that has some calculated fields in the following way:

If the user has entered a one-off price, use this, otherwise
calculate the value based on retail price and dealer discount

using the following type of formula:

FinalPrice: IIf(IsNull([OneOff], round([DiscountPrice],2), [OneOff])

Where I'm expecting to see a rounded version of the DiscountPrice, or the OneOff price, as explained above.

My problem is that for SOME values of OneOff, I am seeing some spurious numbers - in the query, for example, 192.06 is displayed as 192.059997558594 and 28.55 as 28.5499992370605. If I manually change 192.06 to 192, 192 is displayed in the FinalPrice column.

I appreciate that Access has issues calculating certain value for certain data types, but these are NOT calculated values - or at least they shouldn't be. Why would the query be adding these extra decimals to a non-calculated value?

Cheers,
Pete

 

[Gasman]

Perhaps look at Similar threads at the bottom of this thread.
You likely need to pick the correct data type, like Currency.?

 

[CJ_London]

because they are decimals - not just access but any app using decimals. Numbers are stored as binary values 0001=1 0010=2, 0011=3 etc.

When it comes to fractional parts, 0.06 cannot be defined exactly in binary so you get a recurring sequence, potentially to infinity. You'll be OK for values such as 0.1, 0.2, 0.25, etc because they can be resolved in binary in just one or two steps

you can get round the problem by writing your formula slightly differently

FinalPrice: round(nz([OneOff],[DiscountPrice]),2)

 

[zebrafoot]

Many thanks both for taking the time to reply. I think I've been regarding the numbers as a kind of numerical string, rather than considering the issues with storing them.

I appreciate your help.

Pete

 

[Pat Hartman]

If you don't want more than two decimal positions, you have to round in all calculations and save the rounded value. Also, switch from double or single precision to currency. Currency is a scaled integer that supports only 4 decimal places and eliminates the strange rounding issues caused when you use the double or single precision data type. Don't confuse the currency data type with the currency format.

Internally, currency stores 2.49 as 24900. The decimal point is "assumed" to ALWAYS be 4 from the right. So, all calculations are "integer". With the other data types, the number of decimal positions is variable. To get those long decimal values, you stored calculated values without first rounding them.

 

[zebrafoot]

If you don't want more than two decimal positions, you have to round in all calculations and save the rounded value. Also, switch from double or single precision to currency. Currency is a scaled integer that supports only 4 decimal places and eliminates the strange rounding issues caused when you use the double or single precision data type. Don't confuse the currency data type with the currency format.

Internally, currency stores 2.49 as 24900. The decimal point is "assumed" to ALWAYS be 4 from the right. So, all calculations are "integer". With the other data types, the number of decimal positions is variable. To get those long decimal values, you stored calculated values without first rounding them.

Hi Pat, and thank you for your reply to my question.

I'd been reluctant to use currency, because due to the international nature of our business, we deal with dollars, pounds and euros, and the currency seems to default to my local currency (GBP) if I set the datatype to currency. I had not appreciated that I could re-format within the form to change that back to our preferred style, which is "standard".

Thanks once again.

 

[Pat Hartman]

I use the currency data type for my percentages also which always end up with a gazillion decimal places with double/single precision. Currency rounds them to 4 decimal places automatically. If you want to limit to two, you MUST add your own rounding as part of every calculation. Formatting does not affect internal values. It only fools you into thinking the stored value is different from what it actually is which will cause you hours of unnecessary debugging when you are looking for pennies. NEVER, EVER format the table itself. Limit any formatting to the forms and reports. But remember, when you are adding a column of numbers that are stored with four decimal digits, the actual total may vary from your calculated total if the user uses a calculator as mine sometimes do

 

[gemma-the-husky]

Are you consistent between single and double data types? In general for many real numbers I use currency for the precision it brings. Whats the datatype of [finalprice] and [one-off]?

It's important to understand that calculations involving real numbers can introduce rounding errors, as numbers such as 0.1 and 1/3 cannot be perfectly represented in binary.

That's presumably the issue with the [one-off] value. Maybe the implied type is being changed by your calculation, and that introduces the round off issue.

 

[Pat Hartman]

Let me emphasize what @gemma-the-husky said - Unless you actually need more than four positions for decimals, use the Currency data type. It avoids ALL the issues with floating point calculations. You don't need to specify currency as the format just because it is the default.

 

[GPGeorge]

Hi Pat, and thank you for your reply to my question.

I'd been reluctant to use currency, because due to the international nature of our business, we deal with dollars, pounds and euros, and the currency seems to default to my local currency (GBP) if I set the datatype to currency. I had not appreciated that I could re-format within the form to change that back to our preferred style, which is "standard".

Thanks once again.

Don't confuse "currency data type" with "currency format for display".

Both $222.22 and £222.22 refer to the same numeric value (disregarding exchange rates, which is not relevant here)

One is formatted as US currency, the other is formatted as British currency.

They are the same currency data type with a value of 222.22, but they are two of many different possible currency formats, which are used for display purposes.