Solved

Overflow error for MS Access 2000 Query

Posted on 2004-09-07
10
769 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
Comment Utility
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
Comment Utility
is [BUDAMT] value a zero in the data you are using?

jaffer
0
 

Author Comment

by:mrong
Comment Utility
jaffer,

[BUDAMT] has a zero value sometimes and I think that caused the error, but how to fix it?
Thanks.
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility

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
Comment Utility
I made the change but still get "overflow" error.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 27

Expert Comment

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

Author Comment

by:mrong
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

771 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

11 Experts available now in Live!

Get 1:1 Help Now