Precision difficulties

[James Martin]

Hello there,

I have a table that Assays (single) and Weights (long integer). At various
places in the database I need to multiply assays by weights. However, I am
getting slight errors when the weights are large. Upon investigation, I've
found the cause.

Suppose I write an assay of 1.234 to the table and then retrieve it into a
variable called sngAssay.

? sngAssay yields 1.234

But

? sngAssay * 1000000 yields something like 1234000.0069

Change things to double precision doesn't help.

I can fix the problem by rounding the assay before using it in a
calculation. But, given the zillions of places I use assays in calculations
of various kinds, I was wondering if there's a simpler way to fix the
problem.

Any advice would be greatly appreciated.

Thanks in advance.

James

 

[Damon Heron]

Is there a problem with making Weights a single precision number?

Damon

 

[Marshall Barton]

I have a table that Assays (single) and Weights (long integer). At various
places in the database I need to multiply assays by weights. However, I am
getting slight errors when the weights are large. Upon investigation, I've
found the cause.

Suppose I write an assay of 1.234 to the table and then retrieve it into a
variable called sngAssay.

? sngAssay yields 1.234

But

? sngAssay * 1000000 yields something like 1234000.0069

Change things to double precision doesn't help.

I can fix the problem by rounding the assay before using it in a
calculation. But, given the zillions of places I use assays in calculations
of various kinds, I was wondering if there's a simpler way to fix the
problem.

That's a common issue with floating point numbers
(regardless of your software or system or even pencil and
paper). Using Double for the field size in the table should
make the error smaller, but the possibility will still be
there.

If you can live within 4 decinal places, you can avoid the
issue by changing the table field to type Currency.

 

[onedaywhen]

That's a common issue with floating point numbers
(regardless of your software or system or even pencil and
paper). Using Double for the field size in the table should
make the error smaller, but the possibility will still be
there.

If you can live within 4 decinal places, you can avoid the
issue by changing the table field to typeCurrency.

Or if the OP doesn't want their data to appear to be money, or they
need exactly three decimal places with no rounding, or they need four
decimal places with a rounding algorithm other than banker's rounding,
or they need five or more decimal places, or they have some other
reason for avoiding CURRENCY for use with non-money data, then they
should consider using the DECIMAL data type, Jet's native fixed point
numeric type.

Jamie.

--