By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,886 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,886 IT Pros & Developers. It's quick & easy.

URGENT: Error in Summing Values in a Query!

100+
P: 121
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:
Expand|Select|Wrap|Line Numbers
  1. UPDATE ETL_TBL SET ETL_TBL.ERR_CD = "802"
  2. 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:
Expand|Select|Wrap|Line Numbers
  1. <>([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!
May 4 '07 #1
Share this Question
Share on Google+
2 Replies


100+
P: 121
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...
May 4 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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
May 4 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.