Union Query Rounding Issue

Question

My Union Query is rounding the results for fields with a data type of calculated.  How to I stop the Union Query from rounding my results?

Answer 1

Hi Bednarczyk,

I am writing this message to follow up this thread. Did you go through all the replies above? If there any updates, please feel free to share with us here at your convenience. Thank you.

Best regards,

Dihao

Answer 2

Not much info here, but you may have to retrieve the source fields of the calculated fields, then repeat the calculations in the query.

Answer 3

I don't know which of these fields are calculated fields, and I don't know the expected data type of each of those fields.

So I generalize, and let's say your calculated field name is myCalcField, and let's further assume that it is supposed to be a Double rather than Integer.

Then I use the CDbl function around that field, so it converts the value to Double"

CDbl(myCalcField)

This assumes of course that the problem is not with the calculated field itself where a thing called Type Propagation can turn a number into Integer where that was not expected.

Answer 4

I do not understand.  The following is my query:

SELECT

ID,PRVDR_NUM **,**TOT_CHRG,LAB_RT,NLAB_RT,WGE_INDEX,

FROM PROV_DFP;

UNION

The field names in bold do not round

The field names in italics round

Can you show me where to insert

"select cdbl(myCalcField) from table1

union

myCalcField from table2"

Thank you for your help.

Answer 5

maybe explicitly state the data type in the first select clause:

select cdbl(myCalcField) from table1

union

select myCalcField from table2