URGENT: Error in Summing Values in a Query!

[JHNielson]

I Know I've posted an Urgent message before. But I'm in the middle of system testing, and these little stupid bugs are killing me......


I have a query that checks that a set of values (the values in 6 fields) are actually equal to the total they submitted on the form.

The fields are set to Double with Auto for # decimal places. But I've also tried it with them set to 2-Decimal places.


The SQL query looks like this:

UPDATE ETL_TBL SET ETL_TBL.ERR_CD = "802"
WHERE (((ETL_TBL.SPEKR_TOTL_AMT)<>([SPEKR_Fee_AMT]+[SPEKR_Air_AMT]+[SPEKR_HTL_AMT]+[SPEKR_Taxi_AMT]+[SPEKR_PRMN_AMT]+[SPEKR_OTHR_AMT])) AND ((ETL_TBL.OVRD_VALDN_IND)=No) AND ((ETL_TBL.VALD_STA_IND)=No));

The Criteria I put in the Access design view is:

<>([SPEKR_Fee_AMT]+[SPEKR_Air_AMT]+[SPEKR_HTL_AMT]+[SPEKR_Taxi_AMT]+[SPEKR_PRMN_AMT]+[SPEKR_OTHR_AMT])

Now on some of my records - (and I stress some - because not every record is giving me the error), the query says that the totals are incorrect. even though they look right. The fact it only does it on some is killing me even more!

Is there a reason this might be? Is there a different way to do this? Is there some other way to check?


Thanks for any help I can get ASAP!

[JHNielson]

Well I solved it, so I just thought I would share the answer in case anyone else had this problem.

Basically I came to realize that Access was seeing these numbers as much more detail than simply to the second decimal. So the numbers were never perfectly matching out to the nth decimal.

So I but the criteria as Round([...],2)<>Round([...]+[...]+[...]),2

And that worked...

[MMcCarthy]

Well I solved it, so I just thought I would share the answer in case anyone else had this problem.

Basically I came to realize that Access was seeing these numbers as much more detail than simply to the second decimal. So the numbers were never perfectly matching out to the nth decimal.

So I but the criteria as Round([...],2)<>Round([...]+[...]+[...]),2

And that worked...

Glad you got it sorted. Sorry I wasn't around when you posted. Testing time is fun. :)

Mary