Solved

Overflow error for MS Access 2000 Query

Posted on 2004-09-07
10
774 Views
Last Modified: 2012-05-05
Hi Experts,

I have the following query in Access 2000 and gets 'overflow' error when I tried to run it.
My query was working fine untill I added->WHERE ((([ACTEXP]+[COMEXP])/[BUDAMT])>0.8)
I only want select the record if its ([ACTEXP]+[COMEXP])/[BUDAMT] >80%

Thanks in advance!


SELECT [Acctstat].[FUNDAC], [Fundacs].[DESC] AS Fundacs_DESC, [Acctstat].[OBJ], [Objcodes].[DESC] AS Objcodes_DESC, [Acctstat].[BUDAMT], Sum(IIf([req query for acctstat calculations]![OBJ]=[Acctstat]![OBJ] And [req query for acctstat calculations]![FUNDAC]=[Acctstat]![FUNDAC],[req query for acctstat calculations]![EXPAMT],0)) AS ACTEXP, Sum(IIf([req query for acctstat calculations]![OBJ]=[Acctstat]![OBJ] And [req query for acctstat calculations]![FUNDAC]=[Acctstat]![FUNDAC],[req query for acctstat calculations]![REQBAL],0)) AS COMEXP, [BUDAMT]-[ACTEXP] AS ACTBAL, ([BUDAMT]-[ACTEXP])-[COMEXP] AS AVAILBAL, IIf([BUDAMT]<>0,100*[ACTEXP]/[BUDAMT],0) AS PERACT, IIf([BUDAMT]<>0,(IIf([BUDAMT]=0 And [COMEXP]=0,1,100*([ACTEXP]+[COMEXP])/[BUDAMT])),0) AS PERAVAIL
FROM [req query for acctstat calculations], (Acctstat INNER JOIN Objcodes ON [Acctstat].[OBJ]=[Objcodes].[OBJ]) INNER JOIN Fundacs ON [Acctstat].[FUNDAC]=[Fundacs].[FUNDAC]
WHERE ((([ACTEXP]+[COMEXP])/[BUDAMT])>0.8)
GROUP BY [Acctstat].[FUNDAC], [Fundacs].[DESC], [Acctstat].[OBJ], [Objcodes].[DESC], [Acctstat].[BUDAMT], [BUDAMT]-[ACTEXP], ([BUDAMT]-[ACTEXP])-[COMEXP], IIf([BUDAMT]<>0,100*[ACTEXP]/[BUDAMT],0), IIf([BUDAMT]<>0,(IIf([BUDAMT]=0 And [COMEXP]=0,1,100*([ACTEXP]+[COMEXP])/[BUDAMT])),0), [req query for acctstat calculations].[FUNDAC]
HAVING ((([req query for acctstat calculations].FUNDAC)=[Acctstat]![FUNDAC]))
ORDER BY [Acctstat].[FUNDAC], [Acctstat].[OBJ];
0
Comment
Question by:mrong
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 12000379
Check the field definitions on all these fields.  An Overflow error is where you try to populate a field with a certain field type with a number that is beyond what it's capable of.

For example, Integer can handle -32767 to +32768, so if you try to feed it 200,000, you get an overflow error.
Byte can handle 0 to 255, so if you try to feed it 500 or -5, you get an overflow error.

You get the idea.

Hope this helps.
-Jim
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12001028
is [BUDAMT] value a zero in the data you are using?

jaffer
0
 

Author Comment

by:mrong
ID: 12004951
jaffer,

[BUDAMT] has a zero value sometimes and I think that caused the error, but how to fix it?
Thanks.
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 27

Expert Comment

by:jjafferr
ID: 12004997

What do you want to do if [BUDAMT] has a zero value?

try to modify this example:

WHERE ([BUDAMT] <>0 and (([ACTEXP]+[COMEXP])/[BUDAMT])>0.8)
0
 

Author Comment

by:mrong
ID: 12005111
I made the change but still get "overflow" error.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12005134
is any of data a NULL in one of these fields:
[ACTEXP] OR [COMEXP] OR [BUDAMT]
0
 

Author Comment

by:mrong
ID: 12005360
Both [ACTEXP] and [COMEXP] can be either Null or zero. They are just calculation from other fields.
Thanks.
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 50 total points
ID: 12005467
try

WHERE ([ACTEXP]>0 and [COMEXP]>0 and [BUDAMT] >0 and (([ACTEXP]+[COMEXP])/[BUDAMT])>0.8)

OR try the  other way around

WHERE (IsNull([ACTEXP]) = False And IsNull([COMEXP]) = False And IsNull([BUDAMT]) = False And [ACTEXP] <> 0 And [COMEXP] <> 0 And [BUDAMT] <> 0 And (([ACTEXP] + [COMEXP]) / [BUDAMT]) > 0.8)
0
 

Author Comment

by:mrong
ID: 12006319
Actually I want use-> WHERE (Sum([ACTEXP])>0 And Sum([COMEXP])>0 And Sum([BUDAMT])>0 And ((Sum([ACTEXP])+Sum([COMEXP]))/Sum([BUDAMT]))>0.8)

Got error: can't have aggregate function in where clause.

0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12024697
Hi mrong

Don't put the Sum in the Where statement,
make another field in the query, call it SumACTEXP, like this:
SumACTEXP: dsum("[ACTEXP]","TableName", where condition if any)
SumCOMEXP: dsum("[COMEXP]","TableName", where condition if any)
SumBUDAMT: dsum("[BUDAMT]","TableName", where condition if any)

then your Where statement should look like this:

WHERE ([SumACTEXP]>0 and [SumCOMEXP]>0 and [SumBUDAMT] >0 and (([ACTEXP]+[COMEXP])/[BUDAMT])>0.8)


Alternatively, you can put the DSUM statements in the Form where you are calling the Query from and refere to it like this:

WHERE (Forms![FormName]![SumACTEXP]>0 and Forms![FormName]![SumCOMEXP]>0 and Forms![FormName]![SumBUDAMT] >0 and (([ACTEXP]+[COMEXP])/[BUDAMT])>0.8)


jaffer
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question