SQL Data Format

[wizcow]

Hi

I am combining two querys with a union query.
qryRetail and qryVisit
Both querys have cost columns that are set to Currency.

When I run the union query I lose the formatting. I wind up with the data having several decimal places at times. This causes that annoying one cent discrepancey on my reports.

Can I somehow keep the Currency format?

Thanks
Tom

 

[llkhoutx]

Forget about formatting in your queries. Format your report textboxes.

 

[wizcow]

llkhoutx

I tried formating just the report, but I still get the 'one cent discrepancey' in my results.

example: If the query adds these values...

351.7609803
41.9039927
6.44755785
117.7141347

The result is 517.82666555 which rounds-off to 517.83

If the query adds these values...

351.76
41.90
6.45
117.71

The result is 517.82

In accounting they tell me one cent is a sin.

I am not sure why my union query is giving me the long numbers, as the table and the underlying querys are all set to currency.

I'm lost!
Tom

 

[llkhoutx]

It's a rounding error.

Search this site, there's several solutions and references to Microsoft whitepapers on the subject.

 

[wizcow]

llkhoutx

I did as you suggested and found this white paper;
ACC2000: Rounding Errors When You Use Floating-Point Numbers

It says to change the data to 'currency' to correct the problem.

So I'm back to square one.

Can I force a UNION QUERY to produce the CURRENCY format?

Thanks
Tom

 

[wizcow]

Hey I got it!

I went to the underlying querys and added the format like this...

Retail: FORMAT([Cost]*[Quantity],"CURRENCY")

Thanks for the help
Tom

 

[llkhoutx]

I'm glad that you were able to find the solution. Many can't/won't.