MS access 2007 calculated field error

Question

I have an MS Access 2007 query with 3 calculated fields. One adds the totals from 3 fields (bucket 4, bucket 5 and bucket 6) called "Buckets 4-6 Total" and the other does the same but for fields based on a different date, I called it "Date 2 Buckets 4-6 Total" these two fields work as expected, the problem I have is with the third field that subtracts "Buckets 4-6 Total" minus "Date 2 Buckets 4-6 Total".

This works for most of the calculations when the totals are 0 - 0, or anything such as 400 - 200 or even 400 - (1000) for example, but for calculations when both "Buckets 4-6 Total" and "Date 2 Buckets 4-6 Total" are the same (no change), the field returns invalid values such as: 4.54747350886464E-13 or 9.09494701772928E-13 and it is also ignoring my format so that it shows 2 decimal places.

I should also add that this query is made up of 4 different query, and the "Total" fields mentioned above get the total from 3 different fields that come from another query already summed up based on other criteria, not sure if that's where the problem stems from.

Any help is appreciated.

Answer 1

Your problem is bit errors from floating point operations.

Change the data type from Double to Currency, or convert to Currency before performing the subtraction:

BucketDifference: CCur([Buckets 4-6 Total]-CCur([Date 2 Buckets 4-6 Total])

Answer 2

Your problem likely stems from using so many queries and the error could be in any of the individual queries. It's easy to lose track of how the logic of several queries might combine, and I find that Access doesn't always do well referencing layered queries, particularly when there's a lot of them involved.

If you can read SQL at all you can look in the SQL view of your queries and make sure the code is doing the exact calculations you think you set up in design view (since you didn't post code I assume you didn't code it yourself).

If you write SQL the best approach would be to combine all the logic into one larger query, where you can make sure Access isn't screwing something up when referencing the different queries.

Without seeing your query code I can't give you more specific advice.