Solved

Overflow error for MS Access 2000 Query

Posted on 2004-09-07
10
770 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now