"Overflow" error message (1 Viewer)

JH40

Registered User.
Local time
Today, 05:50
Joined
Sep 16, 2010
Messages
100
I am hoping someone may be able to spot the error in my expression below that is resulting in the "Overflow" Access 2007 error message:

Collection Percent: -Round(IIf([Production]<1,0,Sum([Production]/[PP])),2)

This is a select query (not updating any tables) and [Production] and [PP] are two fields available in the table. The result I'm shooting for is that if [Production] is zero, indicate zero, otherwise compute the percent of collection. ([PP] is the amount collected and is a negative number in the table).

Pretty stumped here... Thank you!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:50
Joined
Aug 30, 2003
Messages
36,118
Is there a chance PP is zero or Null?
 

JH40

Registered User.
Local time
Today, 05:50
Joined
Sep 16, 2010
Messages
100
Yes, it definitely could be zero. The table doesn't have any null values so if there are no monies yet collected, it would be zero.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:50
Joined
Aug 30, 2003
Messages
36,118
Dividing by zero will cause an error, so you need to add a test for that as well.
 

JH40

Registered User.
Local time
Today, 05:50
Joined
Sep 16, 2010
Messages
100
Got it. Thank you. Can I write two IIF statements in this one expression? The below revision doesn't work but am I on the right track?

-Round(IIf([Production]<1 Or IIF([PP]<1,0,Sum([Production]/Nz([PP],0)))),2)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:50
Joined
Aug 30, 2003
Messages
36,118
You can nest IIf()'s, but I don't think you need to. Try

IIf([Production]<1 OR [PP] = 0,0,Sum([Production]/[PP]))

Based on your original post, PP can be a negative number so testing for less than 1 wouldn't work.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Sep 12, 2006
Messages
15,614
yuo need to nest 2 iifs


iif(condition1, successresult, iif(condition2, different success result, fail result))
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:50
Joined
Aug 30, 2003
Messages
36,118
yuo need to nest 2 iifs


iif(condition1, successresult, iif(condition2, different success result, fail result))

I don't see the need for a second IIf(); the OR should handle it.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Sep 12, 2006
Messages
15,614
I really meant the syntax needed for multiple iifs would be to nest them.
 

JH40

Registered User.
Local time
Today, 05:50
Joined
Sep 16, 2010
Messages
100
The recommendation makes sense that the [PP] should be = 0. I've pasted in:

IIf([Production]<1 OR [PP] = 0,0,Sum([Production]/[PP]))

Unfortunately, I'm still getting the overflow message... Any other thoughts?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:50
Joined
Aug 30, 2003
Messages
36,118
Can you post a sample db that shows the error?
 

boblarson

Smeghead
Local time
Today, 05:50
Joined
Jan 12, 2001
Messages
32,059
Shouldn't this:

Code:
IIf([Production]<1 OR [PP] = 0,0,Sum([Production]/[PP]))

be this:
Code:
IIf(([Production]<1 [COLOR=red][B]AND[/B][/COLOR] [PP] = 0)[B][COLOR=red] OR [PP] = 0[/COLOR][/B],0,Sum([Production]/[PP]))

I'll tell you why. If you don't test for both, then if Production is >1 PP could still be 0 and that is the problem. :)
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:50
Joined
Aug 30, 2003
Messages
36,118
I don't think so, but it wouldn't be the first time I was wrong. Not even the first time today. :p

I don't agree with "then if Production is >1 PP could still be 0 and that is the problem". With the OR test, if PP is 0 then the condition is True and 0 should be returned, regardless of the Production value.
 

boblarson

Smeghead
Local time
Today, 05:50
Joined
Jan 12, 2001
Messages
32,059
I don't think so, but it wouldn't be the first time I was wrong. Not even the first time today. :p

I don't agree with "then if Production is >1 PP could still be 0 and that is the problem". With the OR test, if PP is 0 then the condition is True and 0 should be returned, regardless of the Production value.

Well, since it was stated that it wasn't working and still getting the error I was assuming that somehow a [PP] value of 0 was still getting through since it has been stated that there are no nulls.

I would start off with just

IIf([PP]=0,0,1)

just to see what happens.

Then try

IIf([PP]=0,0,[Production]/[PP])

before going on. Now that I think about it - it may be the SUM part that is causing the issue. (Perhaps)?????
 

JH40

Registered User.
Local time
Today, 05:50
Joined
Sep 16, 2010
Messages
100
Thank you all for the responses. I've tried the many solutions recommended in the replies but I'm still getting the error messages. I've attached the table/query that I've been using. Hopefully it will be something simple I've overlooked. If you wouldn't mind taking a look, I'd be grateful. Thank you!
 

Attachments

  • Database3.accdb
    388 KB · Views: 219

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:50
Joined
Aug 30, 2003
Messages
36,118
Part of your problem is that PP is text, not number. I'd change that, but for now does this work for you?

SELECT tblBasic.Clinic_ID, Sum(IIf([Production]<1 OR [PP] = "0",0,[Production]/[PP])) AS [Collection Percent]
FROM tblBasic
GROUP BY tblBasic.Clinic_ID
ORDER BY tblBasic.Clinic_ID;
 

smig

Registered User.
Local time
Today, 14:50
Joined
Nov 25, 2009
Messages
2,209
regardles of the Zero problem, what the SUM for ?
 

JH40

Registered User.
Local time
Today, 05:50
Joined
Sep 16, 2010
Messages
100
Thank you all. The quotations around the zero worked! You're help has been much appreciated and I have gained some experience as a result. Watch out, I'm sure I'll be back...
 

Users who are viewing this thread

Top Bottom