asked on

Overflow error for MS Access 2000 Query

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];

Jim Horn

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

jjafferr

is [BUDAMT] value a zero in the data you are using?

jaffer

mrong

ASKER

jaffer,

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

jjafferr

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)

mrong

ASKER

I made the change but still get "overflow" error.

jjafferr

is any of data a NULL in one of these fields:
[ACTEXP] OR [COMEXP] OR [BUDAMT]

mrong

ASKER

Both [ACTEXP] and [COMEXP] can be either Null or zero. They are just calculation from other fields.
Thanks.

ASKER CERTIFIED SOLUTION

jjafferr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mrong

ASKER

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.

jjafferr

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