Solved

Overflow error for MS Access 2000 Query

Posted on 2004-09-07
10
773 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 65

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

734 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