things aren't adding up

[JohnLute]

I have a very complex query with many subqueries that reference numeric
fields either directly or through expressions and aliases. Every numeric
field or expression/alias is formatted to Fixed/4 decimals.

Here's some results that I get:

[UNNtWt] 0.8034 * [field2] 6 = 4.8206 when it should be 4.8204
[UNNtWt] 0.8034 * [field3] 1512 = 1214.7408 when it should be 1214.7975

UNNtWt: IIf(Nz([UnitNtWtConvlb],0)<>0,[UnitNtWtConvlb],IIf([UnitNtWtConvlb]
Is Null,[Density]*[UnitNtWtConvgal]))

UNNtWt is formatted to Fixed/4 decimals however is this string the problem?
the 0.8034 is entirely correct but maybe the string is affecting the accuracy
of the other calculations?

Thanks again!

 

[Jeff Boyce]

What are the underlying fields' data types? If you are using Single or
Double fields, there are "rounding errors" that accompany using those data
types.

Unless you have a need for more than 4 decimal places of precision, consider
using the "Currency" data type ... contrary to its name, it is NOT limited
to currency.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JohnLute]

Hi, Jeff!

What are the underlying fields' data types? If you are using Single or
Double fields, there are "rounding errors" that accompany using those data
types.

They are Number/Double/Fixed/4 Decimals

Unless you have a need for more than 4 decimal places of precision, consider
using the "Currency" data type ... contrary to its name, it is NOT limited
to currency.

Do you mean to change the data type in the table fields to currency? That
would require A LOT of work.

Or do you mean to add "CCur" to the query fields...?

Thanks!

I have a very complex query with many subqueries that reference numeric
fields either directly or through expressions and aliases. Every numeric
field or expression/alias is formatted to Fixed/4 decimals.

Here's some results that I get:

[UNNtWt] 0.8034 * [field2] 6 = 4.8206 when it should be 4.8204
[UNNtWt] 0.8034 * [field3] 1512 = 1214.7408 when it should be 1214.7975

UNNtWt:
IIf(Nz([UnitNtWtConvlb],0)<>0,[UnitNtWtConvlb],IIf([UnitNtWtConvlb]
Is Null,[Density]*[UnitNtWtConvgal]))

UNNtWt is formatted to Fixed/4 decimals however is this string the
problem?
the 0.8034 is entirely correct but maybe the string is affecting the
accuracy
of the other calculations?

Thanks again!

 

[Jeff Boyce]

Why not try coercing the values in your query using CCur() first? If that
doesn't work, you may need to go back to the fields themselves.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi, Jeff!

What are the underlying fields' data types? If you are using Single or
Double fields, there are "rounding errors" that accompany using those
data
types.

They are Number/Double/Fixed/4 Decimals

Unless you have a need for more than 4 decimal places of precision,
consider
using the "Currency" data type ... contrary to its name, it is NOT
limited
to currency.

Do you mean to change the data type in the table fields to currency? That
would require A LOT of work.

Or do you mean to add "CCur" to the query fields...?

Thanks!

I have a very complex query with many subqueries that reference numeric
fields either directly or through expressions and aliases. Every
numeric
field or expression/alias is formatted to Fixed/4 decimals.

Here's some results that I get:

[UNNtWt] 0.8034 * [field2] 6 = 4.8206 when it should be 4.8204
[UNNtWt] 0.8034 * [field3] 1512 = 1214.7408 when it should be 1214.7975

UNNtWt:
IIf(Nz([UnitNtWtConvlb],0)<>0,[UnitNtWtConvlb],IIf([UnitNtWtConvlb]
Is Null,[Density]*[UnitNtWtConvgal]))

UNNtWt is formatted to Fixed/4 decimals however is this string the
problem?
the 0.8034 is entirely correct but maybe the string is affecting the
accuracy
of the other calculations?

Thanks again!

 

[JohnLute]

Thanks, Jeff.

Why not try coercing the values in your query using CCur() first? If that
doesn't work, you may need to go back to the fields themselves.

How would I "coerce" this:
UNNtWt: IIf(Nz([UnitNtWtConvlb],0)<>0,[UnitNtWtConvlb],IIf([UnitNtWtConvlb]
Is Null,[Density]*[UnitNtWtConvgal]))

Like this:
UNNtWt:
IIf(Nz([UnitNtWtConvlb],0)<>0,CCur([UnitNtWtConvlb],IIf([UnitNtWtConvlb]
Is Null,CCur([Density]*[UnitNtWtConvgal]))))

This is returning wrong number of arguments so I'm clueless!

Thanks!

 

[Jeff Boyce]

John

Sorry, "technical term"...

In a query I would convert each field to a CCur() value before using it in a
calculation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks, Jeff.

Why not try coercing the values in your query using CCur() first? If
that
doesn't work, you may need to go back to the fields themselves.

How would I "coerce" this:
UNNtWt:
IIf(Nz([UnitNtWtConvlb],0)<>0,[UnitNtWtConvlb],IIf([UnitNtWtConvlb]
Is Null,[Density]*[UnitNtWtConvgal]))

Like this:
UNNtWt:
IIf(Nz([UnitNtWtConvlb],0)<>0,CCur([UnitNtWtConvlb],IIf([UnitNtWtConvlb]
Is Null,CCur([Density]*[UnitNtWtConvgal]))))

This is returning wrong number of arguments so I'm clueless!

Thanks!