Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-04-2010, 02:16 PM   #1
JH40
Newly Registered User
 
Join Date: Sep 2010
Posts: 100
Thanks: 1
Thanked 0 Times in 0 Posts
JH40 is on a distinguished road
"Overflow" error message

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!

JH40 is offline   Reply With Quote
Old 11-04-2010, 02:25 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,218
Thanks: 10
Thanked 3,903 Times in 3,846 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "Overflow" error message

Is there a chance PP is zero or Null?
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-04-2010, 02:29 PM   #3
JH40
Newly Registered User
 
Join Date: Sep 2010
Posts: 100
Thanks: 1
Thanked 0 Times in 0 Posts
JH40 is on a distinguished road
Re: "Overflow" error message

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.

JH40 is offline   Reply With Quote
Old 11-04-2010, 02:31 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,218
Thanks: 10
Thanked 3,903 Times in 3,846 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "Overflow" error message

Dividing by zero will cause an error, so you need to add a test for that as well.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-04-2010, 02:39 PM   #5
JH40
Newly Registered User
 
Join Date: Sep 2010
Posts: 100
Thanks: 1
Thanked 0 Times in 0 Posts
JH40 is on a distinguished road
Re: "Overflow" error message

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)
JH40 is offline   Reply With Quote
Old 11-04-2010, 02:52 PM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,218
Thanks: 10
Thanked 3,903 Times in 3,846 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "Overflow" error message

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-04-2010, 02:53 PM   #7
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,463
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: "Overflow" error message

yuo need to nest 2 iifs


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

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 11-04-2010, 03:02 PM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,218
Thanks: 10
Thanked 3,903 Times in 3,846 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "Overflow" error message

Quote:
Originally Posted by gemma-the-husky View Post
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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-04-2010, 04:04 PM   #9
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,463
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: "Overflow" error message

I really meant the syntax needed for multiple iifs would be to nest them.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 11-08-2010, 09:14 AM   #10
JH40
Newly Registered User
 
Join Date: Sep 2010
Posts: 100
Thanks: 1
Thanked 0 Times in 0 Posts
JH40 is on a distinguished road
Re: "Overflow" error message

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?
JH40 is offline   Reply With Quote
Old 11-08-2010, 10:06 AM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,218
Thanks: 10
Thanked 3,903 Times in 3,846 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "Overflow" error message

Can you post a sample db that shows the error?
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-08-2010, 10:14 AM   #12
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,817 Times in 1,575 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: "Overflow" error message

Shouldn't this:

Code:
IIf([Production]<1 OR [PP] = 0,0,Sum([Production]/[PP]))
be this:
Code:
IIf(([Production]<1 AND [PP] = 0) OR [PP] = 0,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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by boblarson; 11-08-2010 at 10:15 AM. Reason: left one in that I meant to delete
boblarson is offline   Reply With Quote
Old 11-08-2010, 10:36 AM   #13
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,218
Thanks: 10
Thanked 3,903 Times in 3,846 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "Overflow" error message

I don't think so, but it wouldn't be the first time I was wrong. Not even the first time today.

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-08-2010, 10:57 AM   #14
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,817 Times in 1,575 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Talking Re: "Overflow" error message

Quote:
Originally Posted by pbaldy View Post
I don't think so, but it wouldn't be the first time I was wrong. Not even the first time today.

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)?????
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 11-08-2010, 09:15 PM   #15
JH40
Newly Registered User
 
Join Date: Sep 2010
Posts: 100
Thanks: 1
Thanked 0 Times in 0 Posts
JH40 is on a distinguished road
Re: "Overflow" error message

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!
Attached Files
File Type: accdb Database3.accdb (388.0 KB, 139 views)

JH40 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
getting "overflow" message when querying percentage scottsoo9 Queries 7 12-07-2009 11:36 AM
"Overflow" message at startup Vivitech Modules & VBA 6 05-24-2004 07:43 AM




All times are GMT -8. The time now is 05:36 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World