sum inaccurate 1

[-cush-]

I have a query that sums an amount field. The data type should be currency, but because my export to a text file must not include a dollar sign ($) I am forced to use a double. Anyway when I sum the amount in a query, Access adds .000001 to the value. To test, I added a group to my query. The summed amount was off in three of the groups, but the amount off did not add up to the original .000001 that was off in the original grand total. Why does Access have these discrepancies? Is there a way around them?

 

[SkipVought]

kcushing (Programmer),

As a programmer, you know that float arithmetic has precision limits, yes?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[-cush-]

Yes, I was just hoping for a quick fix, like converting it to currency on the fly or * 100 and int and / 100 or something.

 

[-cush-]

Is there an Access equivalent to SQL Servers Convert?

 

[SkipVought]

Did you try either?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[-cush-]

Really, I don't see why it ever gets off though, because the none of the data that it is summing goes beyond two decimal places.

 

[BigRed1212]

Kcushing-

I read this so I'm not that smart:

Access doesn't have to export the dollar sign.

Create a query that selects everything from the query that has the results you want to export.

Use the format function in the query on the currency like so:
select format(dollarsindollars, '#,##0.00') as dollarsinnumbers, etc

Go to export from that query.

The text will look like
"34.35","25.46","278.36", etc

Select none for a text qualifier on the export and it will come in like
23.21,23.00,45.21 etc

Hope that is helpful. Maybe it lets you do the database the way you want to and then just futz with the export instead of having to change the database to accomodate the export.

 

[-cush-]

Ah, now someones got their head screwed on straight!

Thanks!

 

[PHV]

Why not simply use the CDec function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigRed1212]

Because I had to Google it to figure out what it was?