Overflow Error

I am using Access 2002.  I have a report that creates a bar indicating survey 
response information.  I need to know all the surveys that have been sent and 
their responses, or lack of responses.  That's where the problem lies with 
those that don't respond.

On the report I count and display the number of surveys returned using 
=Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))

Then I figure out and display the response percent using 
=Nz([NeverUsedKnowledge]/[AnsweredUsedKnowledge])

The part that creates the bar is rctNeverUsedBar.Width = 
Nz([NeverUsedKnowledge] / [AnsweredUsedKnowledge]) * (1440 * 4)

This was working great until there was a survey that didn't receive any 
responses and now I receive a Runtime error 6, Overflow.

I'm not a programmer so I really don't know how to handle this error.  I 
tried using the No Data event but since there is one line indicating the 
survey was sent there really is data.  Then I tried using the On Error event 
to send a message but that didn't work either.  Does anyone have any 
suggestions on how I can fix this?  Thank you in advance.
0
Utf
5/21/2010 1:03:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

7 Replies
783 Views

Similar Articles

[PageSpeed] 44

Ann -

The problem is because you are dividing by zero, which has no mathematical 
meaning.  You can test for a zero in your code and handle it like this:

Nz([NeverUsedKnowledge] / nz([AnsweredUsedKnowledge],1)) * (1440 * 4)

I am assuming that AnsweredUsedKnowledge is an integer (count of responses), 
and it will never be less than [NeverUsedKnowledge].  With that assumption, 
if [AnsweredUsedKnowledge] is zero, then [NeverUsedKnowledge] must be zero.  
The nz([],1) added above will change the zero to a 1 in the denominator, 
which prevents the overflow error. 

Remember to do this for both cases where you are dividing by zero.

You can also use logic like this (in cases where the numerator may not be 
zero):
=IF(nz([AnsweredUsedKnowledge],0) = 0,0,Nz([NeverUsedKnowledge] / 
nz([AnsweredUsedKnowledge],1)) * (1440 * 4))

-- 
Daryl S


"Ann" wrote:

> I am using Access 2002.  I have a report that creates a bar indicating survey 
> response information.  I need to know all the surveys that have been sent and 
> their responses, or lack of responses.  That's where the problem lies with 
> those that don't respond.
> 
> On the report I count and display the number of surveys returned using 
> =Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))
> 
> Then I figure out and display the response percent using 
> =Nz([NeverUsedKnowledge]/[AnsweredUsedKnowledge])
> 
> The part that creates the bar is rctNeverUsedBar.Width = 
> Nz([NeverUsedKnowledge] / [AnsweredUsedKnowledge]) * (1440 * 4)
> 
> This was working great until there was a survey that didn't receive any 
> responses and now I receive a Runtime error 6, Overflow.
> 
> I'm not a programmer so I really don't know how to handle this error.  I 
> tried using the No Data event but since there is one line indicating the 
> survey was sent there really is data.  Then I tried using the On Error event 
> to send a message but that didn't work either.  Does anyone have any 
> suggestions on how I can fix this?  Thank you in advance.
0
Utf
5/21/2010 1:19:01 PM
Hi Daryl,

Thanks for the help but I couldn't get it to work.  I still get the overflow 
error.  

I left something off the last time, sorry.  Here is how it looks now.

This is NeverUsedKnowledge and it has remained the same 
=Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))

This is the one I am dividing by, AnsweredUsedKnowledge 
=Count([txtUsedKnowledge]).  In the problem case they are both zeros.

I changed the percent to =Nz([NeverUsedKnowledge] 
/Nz([AnsweredUsedKnowledge],1)) 

The part that creates the bar is now =Nz([NeverUsedKnowledge] 
/Nz([AnsweredUsedKnowledge],1)) * (1440 * 4)


"Daryl S" wrote:

> Ann -
> 
> The problem is because you are dividing by zero, which has no mathematical 
> meaning.  You can test for a zero in your code and handle it like this:
> 
> Nz([NeverUsedKnowledge] / nz([AnsweredUsedKnowledge],1)) * (1440 * 4)
> 
> I am assuming that AnsweredUsedKnowledge is an integer (count of responses), 
> and it will never be less than [NeverUsedKnowledge].  With that assumption, 
> if [AnsweredUsedKnowledge] is zero, then [NeverUsedKnowledge] must be zero.  
> The nz([],1) added above will change the zero to a 1 in the denominator, 
> which prevents the overflow error. 
> 
> Remember to do this for both cases where you are dividing by zero.
> 
> You can also use logic like this (in cases where the numerator may not be 
> zero):
> =IF(nz([AnsweredUsedKnowledge],0) = 0,0,Nz([NeverUsedKnowledge] / 
> nz([AnsweredUsedKnowledge],1)) * (1440 * 4))
> 
> -- 
> Daryl S
> 
> 
> "Ann" wrote:
> 
> > I am using Access 2002.  I have a report that creates a bar indicating survey 
> > response information.  I need to know all the surveys that have been sent and 
> > their responses, or lack of responses.  That's where the problem lies with 
> > those that don't respond.
> > 
> > On the report I count and display the number of surveys returned using 
> > =Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))
> > 
> > Then I figure out and display the response percent using 
> > =Nz([NeverUsedKnowledge]/[AnsweredUsedKnowledge])
> > 
> > The part that creates the bar is rctNeverUsedBar.Width = 
> > Nz([NeverUsedKnowledge] / [AnsweredUsedKnowledge]) * (1440 * 4)
> > 
> > This was working great until there was a survey that didn't receive any 
> > responses and now I receive a Runtime error 6, Overflow.
> > 
> > I'm not a programmer so I really don't know how to handle this error.  I 
> > tried using the No Data event but since there is one line indicating the 
> > survey was sent there really is data.  Then I tried using the On Error event 
> > to send a message but that didn't work either.  Does anyone have any 
> > suggestions on how I can fix this?  Thank you in advance.
0
Utf
5/21/2010 2:00:03 PM
Ann -

Did you change all code lines that could be divided by zero?  If you only 
change one, then another one could be erroring out.

Try both of these (moved parentheses):

Nz([NeverUsedKnowledge],0) / nz([AnsweredUsedKnowledge],1) * (1440 * 4)

=IF(nz([AnsweredUsedKnowledge],0) = 0,0,Nz([NeverUsedKnowledge]) / 
nz([AnsweredUsedKnowledge],1) * (1440 * 4))

If you get the error, press Ctrl and Break to see which line of the code is 
erroring out.  You can step through the code also by setting a breakpoint on 
an executable line above these and press F8 until you find the offending 
line. 

-- 
Daryl S


"Ann" wrote:

> Hi Daryl,
> 
> Thanks for the help but I couldn't get it to work.  I still get the overflow 
> error.  
> 
> I left something off the last time, sorry.  Here is how it looks now.
> 
> This is NeverUsedKnowledge and it has remained the same 
> =Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))
> 
> This is the one I am dividing by, AnsweredUsedKnowledge 
> =Count([txtUsedKnowledge]).  In the problem case they are both zeros.
> 
> I changed the percent to =Nz([NeverUsedKnowledge] 
> /Nz([AnsweredUsedKnowledge],1)) 
> 
> The part that creates the bar is now =Nz([NeverUsedKnowledge] 
> /Nz([AnsweredUsedKnowledge],1)) * (1440 * 4)
> 
> 
> "Daryl S" wrote:
> 
> > Ann -
> > 
> > The problem is because you are dividing by zero, which has no mathematical 
> > meaning.  You can test for a zero in your code and handle it like this:
> > 
> > Nz([NeverUsedKnowledge] / nz([AnsweredUsedKnowledge],1)) * (1440 * 4)
> > 
> > I am assuming that AnsweredUsedKnowledge is an integer (count of responses), 
> > and it will never be less than [NeverUsedKnowledge].  With that assumption, 
> > if [AnsweredUsedKnowledge] is zero, then [NeverUsedKnowledge] must be zero.  
> > The nz([],1) added above will change the zero to a 1 in the denominator, 
> > which prevents the overflow error. 
> > 
> > Remember to do this for both cases where you are dividing by zero.
> > 
> > You can also use logic like this (in cases where the numerator may not be 
> > zero):
> > =IF(nz([AnsweredUsedKnowledge],0) = 0,0,Nz([NeverUsedKnowledge] / 
> > nz([AnsweredUsedKnowledge],1)) * (1440 * 4))
> > 
> > -- 
> > Daryl S
> > 
> > 
> > "Ann" wrote:
> > 
> > > I am using Access 2002.  I have a report that creates a bar indicating survey 
> > > response information.  I need to know all the surveys that have been sent and 
> > > their responses, or lack of responses.  That's where the problem lies with 
> > > those that don't respond.
> > > 
> > > On the report I count and display the number of surveys returned using 
> > > =Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))
> > > 
> > > Then I figure out and display the response percent using 
> > > =Nz([NeverUsedKnowledge]/[AnsweredUsedKnowledge])
> > > 
> > > The part that creates the bar is rctNeverUsedBar.Width = 
> > > Nz([NeverUsedKnowledge] / [AnsweredUsedKnowledge]) * (1440 * 4)
> > > 
> > > This was working great until there was a survey that didn't receive any 
> > > responses and now I receive a Runtime error 6, Overflow.
> > > 
> > > I'm not a programmer so I really don't know how to handle this error.  I 
> > > tried using the No Data event but since there is one line indicating the 
> > > survey was sent there really is data.  Then I tried using the On Error event 
> > > to send a message but that didn't work either.  Does anyone have any 
> > > suggestions on how I can fix this?  Thank you in advance.
0
Utf
5/21/2010 4:06:01 PM
You must avoid dividing by zero.  Since you are assigning a control source in 
the report this is a bit more difficult than doing something in a query.

=IIF(Count([AnsweredUsedKnowledge]) = 0,0,
    [NeverUsedKnowledge] / 
IIF(Count([AnsweredUsedKnowledge])=0,1,Count(Count([AnsweredUsedKnowledge]))))

Since you are calculating width, you might be able to use a simpler expression
=[NeverUsedKnowledge] / 
IIF([AnsweredUsedKnowledge]=0,100000000,[AnsweredUsedKnowledge]) * 1440 * 4

By the way NZ does not change zero to some other value.  It changes nulls to 
some specified value.  As far as I can recall Count will not return null.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Ann wrote:
> Hi Daryl,
> 
> Thanks for the help but I couldn't get it to work.  I still get the overflow 
> error.  
> 
> I left something off the last time, sorry.  Here is how it looks now.
> 
> This is NeverUsedKnowledge and it has remained the same 
> =Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))
> 
> This is the one I am dividing by, AnsweredUsedKnowledge 
> =Count([txtUsedKnowledge]).  In the problem case they are both zeros.
> 
> I changed the percent to =Nz([NeverUsedKnowledge] 
> /Nz([AnsweredUsedKnowledge],1)) 
> 
> The part that creates the bar is now =Nz([NeverUsedKnowledge] 
> /Nz([AnsweredUsedKnowledge],1)) * (1440 * 4)
> 
> 
> "Daryl S" wrote:
> 
>> Ann -
>>
>> The problem is because you are dividing by zero, which has no mathematical 
>> meaning.  You can test for a zero in your code and handle it like this:
>>
>> Nz([NeverUsedKnowledge] / nz([AnsweredUsedKnowledge],1)) * (1440 * 4)
>>
>> I am assuming that AnsweredUsedKnowledge is an integer (count of responses), 
>> and it will never be less than [NeverUsedKnowledge].  With that assumption, 
>> if [AnsweredUsedKnowledge] is zero, then [NeverUsedKnowledge] must be zero.  
>> The nz([],1) added above will change the zero to a 1 in the denominator, 
>> which prevents the overflow error. 
>>
>> Remember to do this for both cases where you are dividing by zero.
>>
>> You can also use logic like this (in cases where the numerator may not be 
>> zero):
>> =IF(nz([AnsweredUsedKnowledge],0) = 0,0,Nz([NeverUsedKnowledge] / 
>> nz([AnsweredUsedKnowledge],1)) * (1440 * 4))
>>
>> -- 
>> Daryl S
>>
>>
>> "Ann" wrote:
>>
>>> I am using Access 2002.  I have a report that creates a bar indicating survey 
>>> response information.  I need to know all the surveys that have been sent and 
>>> their responses, or lack of responses.  That's where the problem lies with 
>>> those that don't respond.
>>>
>>> On the report I count and display the number of surveys returned using 
>>> =Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))
>>>
>>> Then I figure out and display the response percent using 
>>> =Nz([NeverUsedKnowledge]/[AnsweredUsedKnowledge])
>>>
>>> The part that creates the bar is rctNeverUsedBar.Width = 
>>> Nz([NeverUsedKnowledge] / [AnsweredUsedKnowledge]) * (1440 * 4)
>>>
>>> This was working great until there was a survey that didn't receive any 
>>> responses and now I receive a Runtime error 6, Overflow.
>>>
>>> I'm not a programmer so I really don't know how to handle this error.  I 
>>> tried using the No Data event but since there is one line indicating the 
>>> survey was sent there really is data.  Then I tried using the On Error event 
>>> to send a message but that didn't work either.  Does anyone have any 
>>> suggestions on how I can fix this?  Thank you in advance.
0
John
5/21/2010 4:06:21 PM
I tried the following and received an error:

=IIF(Count([AnsweredUsedKnowledge]) = 0,0,
>     [NeverUsedKnowledge] / 
> IIF(Count([AnsweredUsedKnowledge])=0,1,Count(Count([AnsweredUsedKnowledge]))))

"Cannot have aggregate function in expression 
(Count(Count([AnsweredUsedKnowledge])))

By the way NZ does not change zero to some other value.  It changes nulls to 
> some specified value.  As far as I can recall Count will not return null.

I thought NZ only changed to zero?  It will change to any number I specify?  
I didn't know about the null.  I got that by Googling but thanks for the 
explanation.
"John Spencer" wrote:

> You must avoid dividing by zero.  Since you are assigning a control source in 
> the report this is a bit more difficult than doing something in a query.
> 
> =IIF(Count([AnsweredUsedKnowledge]) = 0,0,
>     [NeverUsedKnowledge] / 
> IIF(Count([AnsweredUsedKnowledge])=0,1,Count(Count([AnsweredUsedKnowledge]))))
> 
> Since you are calculating width, you might be able to use a simpler expression
> =[NeverUsedKnowledge] / 
> IIF([AnsweredUsedKnowledge]=0,100000000,[AnsweredUsedKnowledge]) * 1440 * 4
> 
> By the way NZ does not change zero to some other value.  It changes nulls to 
> some specified value.  As far as I can recall Count will not return null.
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> Ann wrote:
> > Hi Daryl,
> > 
> > Thanks for the help but I couldn't get it to work.  I still get the overflow 
> > error.  
> > 
> > I left something off the last time, sorry.  Here is how it looks now.
> > 
> > This is NeverUsedKnowledge and it has remained the same 
> > =Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))
> > 
> > This is the one I am dividing by, AnsweredUsedKnowledge 
> > =Count([txtUsedKnowledge]).  In the problem case they are both zeros.
> > 
> > I changed the percent to =Nz([NeverUsedKnowledge] 
> > /Nz([AnsweredUsedKnowledge],1)) 
> > 
> > The part that creates the bar is now =Nz([NeverUsedKnowledge] 
> > /Nz([AnsweredUsedKnowledge],1)) * (1440 * 4)
> > 
> > 
> > "Daryl S" wrote:
> > 
> >> Ann -
> >>
> >> The problem is because you are dividing by zero, which has no mathematical 
> >> meaning.  You can test for a zero in your code and handle it like this:
> >>
> >> Nz([NeverUsedKnowledge] / nz([AnsweredUsedKnowledge],1)) * (1440 * 4)
> >>
> >> I am assuming that AnsweredUsedKnowledge is an integer (count of responses), 
> >> and it will never be less than [NeverUsedKnowledge].  With that assumption, 
> >> if [AnsweredUsedKnowledge] is zero, then [NeverUsedKnowledge] must be zero.  
> >> The nz([],1) added above will change the zero to a 1 in the denominator, 
> >> which prevents the overflow error. 
> >>
> >> Remember to do this for both cases where you are dividing by zero.
> >>
> >> You can also use logic like this (in cases where the numerator may not be 
> >> zero):
> >> =IF(nz([AnsweredUsedKnowledge],0) = 0,0,Nz([NeverUsedKnowledge] / 
> >> nz([AnsweredUsedKnowledge],1)) * (1440 * 4))
> >>
> >> -- 
> >> Daryl S
> >>
> >>
> >> "Ann" wrote:
> >>
> >>> I am using Access 2002.  I have a report that creates a bar indicating survey 
> >>> response information.  I need to know all the surveys that have been sent and 
> >>> their responses, or lack of responses.  That's where the problem lies with 
> >>> those that don't respond.
> >>>
> >>> On the report I count and display the number of surveys returned using 
> >>> =Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))
> >>>
> >>> Then I figure out and display the response percent using 
> >>> =Nz([NeverUsedKnowledge]/[AnsweredUsedKnowledge])
> >>>
> >>> The part that creates the bar is rctNeverUsedBar.Width = 
> >>> Nz([NeverUsedKnowledge] / [AnsweredUsedKnowledge]) * (1440 * 4)
> >>>
> >>> This was working great until there was a survey that didn't receive any 
> >>> responses and now I receive a Runtime error 6, Overflow.
> >>>
> >>> I'm not a programmer so I really don't know how to handle this error.  I 
> >>> tried using the No Data event but since there is one line indicating the 
> >>> survey was sent there really is data.  Then I tried using the On Error event 
> >>> to send a message but that didn't work either.  Does anyone have any 
> >>> suggestions on how I can fix this?  Thank you in advance.
> .
> 
0
Utf
5/24/2010 11:49:01 AM
My error. I had a typo with that extra Count() function

=IIF(Count([AnsweredUsedKnowledge]) = 0,0,
    [NeverUsedKnowledge] /
IIF(Count([AnsweredUsedKnowledge])=0,1,Count([AnsweredUsedKnowledge])))

If you do not specify the second argument Nz will change
to Zero for number types
to "" (a zero-length string) for string types
to December 30 1899 at midnight for date types.

So
   Nz(SomeDateField,Date()) Returns the current date if SomeDateField is null
   Nz(SomeDateField,#2999-12-31#) returns the Dec 31 2999 if SomeDateField is null


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Ann wrote:
> I tried the following and received an error:
> 
> =IIF(Count([AnsweredUsedKnowledge]) = 0,0,
>>     [NeverUsedKnowledge] / 
>> IIF(Count([AnsweredUsedKnowledge])=0,1,Count(Count([AnsweredUsedKnowledge]))))
> 
> "Cannot have aggregate function in expression 
> (Count(Count([AnsweredUsedKnowledge])))
> 
> By the way NZ does not change zero to some other value.  It changes nulls to 
>> some specified value.  As far as I can recall Count will not return null.
> 
> I thought NZ only changed to zero?  It will change to any number I specify?  
> I didn't know about the null.  I got that by Googling but thanks for the 
> explanation.
> "John Spencer" wrote:
> 
>> You must avoid dividing by zero.  Since you are assigning a control source in 
>> the report this is a bit more difficult than doing something in a query.
>>
>> =IIF(Count([AnsweredUsedKnowledge]) = 0,0,
>>     [NeverUsedKnowledge] / 
>> IIF(Count([AnsweredUsedKnowledge])=0,1,Count(Count([AnsweredUsedKnowledge]))))
>>
>> Since you are calculating width, you might be able to use a simpler expression
>> =[NeverUsedKnowledge] / 
>> IIF([AnsweredUsedKnowledge]=0,100000000,[AnsweredUsedKnowledge]) * 1440 * 4
>>
>> By the way NZ does not change zero to some other value.  It changes nulls to 
>> some specified value.  As far as I can recall Count will not return null.
>>
>> John Spencer
>> Access MVP 2002-2005, 2007-2010
>> The Hilltop Institute
>> University of Maryland Baltimore County
>>
>> Ann wrote:
>>> Hi Daryl,
>>>
>>> Thanks for the help but I couldn't get it to work.  I still get the overflow 
>>> error.  
>>>
>>> I left something off the last time, sorry.  Here is how it looks now.
>>>
>>> This is NeverUsedKnowledge and it has remained the same 
>>> =Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))
>>>
>>> This is the one I am dividing by, AnsweredUsedKnowledge 
>>> =Count([txtUsedKnowledge]).  In the problem case they are both zeros.
>>>
>>> I changed the percent to =Nz([NeverUsedKnowledge] 
>>> /Nz([AnsweredUsedKnowledge],1)) 
>>>
>>> The part that creates the bar is now =Nz([NeverUsedKnowledge] 
>>> /Nz([AnsweredUsedKnowledge],1)) * (1440 * 4)
>>>
>>>
>>> "Daryl S" wrote:
>>>
>>>> Ann -
>>>>
>>>> The problem is because you are dividing by zero, which has no mathematical 
>>>> meaning.  You can test for a zero in your code and handle it like this:
>>>>
>>>> Nz([NeverUsedKnowledge] / nz([AnsweredUsedKnowledge],1)) * (1440 * 4)
>>>>
>>>> I am assuming that AnsweredUsedKnowledge is an integer (count of responses), 
>>>> and it will never be less than [NeverUsedKnowledge].  With that assumption, 
>>>> if [AnsweredUsedKnowledge] is zero, then [NeverUsedKnowledge] must be zero.  
>>>> The nz([],1) added above will change the zero to a 1 in the denominator, 
>>>> which prevents the overflow error. 
>>>>
>>>> Remember to do this for both cases where you are dividing by zero.
>>>>
>>>> You can also use logic like this (in cases where the numerator may not be 
>>>> zero):
>>>> =IF(nz([AnsweredUsedKnowledge],0) = 0,0,Nz([NeverUsedKnowledge] / 
>>>> nz([AnsweredUsedKnowledge],1)) * (1440 * 4))
>>>>
>>>> -- 
>>>> Daryl S
>>>>
>>>>
>>>> "Ann" wrote:
>>>>
>>>>> I am using Access 2002.  I have a report that creates a bar indicating survey 
>>>>> response information.  I need to know all the surveys that have been sent and 
>>>>> their responses, or lack of responses.  That's where the problem lies with 
>>>>> those that don't respond.
>>>>>
>>>>> On the report I count and display the number of surveys returned using 
>>>>> =Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))
>>>>>
>>>>> Then I figure out and display the response percent using 
>>>>> =Nz([NeverUsedKnowledge]/[AnsweredUsedKnowledge])
>>>>>
>>>>> The part that creates the bar is rctNeverUsedBar.Width = 
>>>>> Nz([NeverUsedKnowledge] / [AnsweredUsedKnowledge]) * (1440 * 4)
>>>>>
>>>>> This was working great until there was a survey that didn't receive any 
>>>>> responses and now I receive a Runtime error 6, Overflow.
>>>>>
>>>>> I'm not a programmer so I really don't know how to handle this error.  I 
>>>>> tried using the No Data event but since there is one line indicating the 
>>>>> survey was sent there really is data.  Then I tried using the On Error event 
>>>>> to send a message but that didn't work either.  Does anyone have any 
>>>>> suggestions on how I can fix this?  Thank you in advance.
>> .
>>
0
John
5/24/2010 1:09:36 PM
Thanks John, that did what I needed.  I really appreciate your help and also 
the explanations.  I don't know a lot about programming but what I do know I 
would like to be correct.

"John Spencer" wrote:

> My error. I had a typo with that extra Count() function
> 
> =IIF(Count([AnsweredUsedKnowledge]) = 0,0,
>     [NeverUsedKnowledge] /
> IIF(Count([AnsweredUsedKnowledge])=0,1,Count([AnsweredUsedKnowledge])))
> 
> If you do not specify the second argument Nz will change
> to Zero for number types
> to "" (a zero-length string) for string types
> to December 30 1899 at midnight for date types.
> 
> So
>    Nz(SomeDateField,Date()) Returns the current date if SomeDateField is null
>    Nz(SomeDateField,#2999-12-31#) returns the Dec 31 2999 if SomeDateField is null
> 
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> Ann wrote:
> > I tried the following and received an error:
> > 
> > =IIF(Count([AnsweredUsedKnowledge]) = 0,0,
> >>     [NeverUsedKnowledge] / 
> >> IIF(Count([AnsweredUsedKnowledge])=0,1,Count(Count([AnsweredUsedKnowledge]))))
> > 
> > "Cannot have aggregate function in expression 
> > (Count(Count([AnsweredUsedKnowledge])))
> > 
> > By the way NZ does not change zero to some other value.  It changes nulls to 
> >> some specified value.  As far as I can recall Count will not return null.
> > 
> > I thought NZ only changed to zero?  It will change to any number I specify?  
> > I didn't know about the null.  I got that by Googling but thanks for the 
> > explanation.
> > "John Spencer" wrote:
> > 
> >> You must avoid dividing by zero.  Since you are assigning a control source in 
> >> the report this is a bit more difficult than doing something in a query.
> >>
> >> =IIF(Count([AnsweredUsedKnowledge]) = 0,0,
> >>     [NeverUsedKnowledge] / 
> >> IIF(Count([AnsweredUsedKnowledge])=0,1,Count(Count([AnsweredUsedKnowledge]))))
> >>
> >> Since you are calculating width, you might be able to use a simpler expression
> >> =[NeverUsedKnowledge] / 
> >> IIF([AnsweredUsedKnowledge]=0,100000000,[AnsweredUsedKnowledge]) * 1440 * 4
> >>
> >> By the way NZ does not change zero to some other value.  It changes nulls to 
> >> some specified value.  As far as I can recall Count will not return null.
> >>
> >> John Spencer
> >> Access MVP 2002-2005, 2007-2010
> >> The Hilltop Institute
> >> University of Maryland Baltimore County
> >>
> >> Ann wrote:
> >>> Hi Daryl,
> >>>
> >>> Thanks for the help but I couldn't get it to work.  I still get the overflow 
> >>> error.  
> >>>
> >>> I left something off the last time, sorry.  Here is how it looks now.
> >>>
> >>> This is NeverUsedKnowledge and it has remained the same 
> >>> =Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))
> >>>
> >>> This is the one I am dividing by, AnsweredUsedKnowledge 
> >>> =Count([txtUsedKnowledge]).  In the problem case they are both zeros.
> >>>
> >>> I changed the percent to =Nz([NeverUsedKnowledge] 
> >>> /Nz([AnsweredUsedKnowledge],1)) 
> >>>
> >>> The part that creates the bar is now =Nz([NeverUsedKnowledge] 
> >>> /Nz([AnsweredUsedKnowledge],1)) * (1440 * 4)
> >>>
> >>>
> >>> "Daryl S" wrote:
> >>>
> >>>> Ann -
> >>>>
> >>>> The problem is because you are dividing by zero, which has no mathematical 
> >>>> meaning.  You can test for a zero in your code and handle it like this:
> >>>>
> >>>> Nz([NeverUsedKnowledge] / nz([AnsweredUsedKnowledge],1)) * (1440 * 4)
> >>>>
> >>>> I am assuming that AnsweredUsedKnowledge is an integer (count of responses), 
> >>>> and it will never be less than [NeverUsedKnowledge].  With that assumption, 
> >>>> if [AnsweredUsedKnowledge] is zero, then [NeverUsedKnowledge] must be zero.  
> >>>> The nz([],1) added above will change the zero to a 1 in the denominator, 
> >>>> which prevents the overflow error. 
> >>>>
> >>>> Remember to do this for both cases where you are dividing by zero.
> >>>>
> >>>> You can also use logic like this (in cases where the numerator may not be 
> >>>> zero):
> >>>> =IF(nz([AnsweredUsedKnowledge],0) = 0,0,Nz([NeverUsedKnowledge] / 
> >>>> nz([AnsweredUsedKnowledge],1)) * (1440 * 4))
> >>>>
> >>>> -- 
> >>>> Daryl S
> >>>>
> >>>>
> >>>> "Ann" wrote:
> >>>>
> >>>>> I am using Access 2002.  I have a report that creates a bar indicating survey 
> >>>>> response information.  I need to know all the surveys that have been sent and 
> >>>>> their responses, or lack of responses.  That's where the problem lies with 
> >>>>> those that don't respond.
> >>>>>
> >>>>> On the report I count and display the number of surveys returned using 
> >>>>> =Count(IIf([txtUsedKnowledge]="Not at all/Never",1,Null))
> >>>>>
> >>>>> Then I figure out and display the response percent using 
> >>>>> =Nz([NeverUsedKnowledge]/[AnsweredUsedKnowledge])
> >>>>>
> >>>>> The part that creates the bar is rctNeverUsedBar.Width = 
> >>>>> Nz([NeverUsedKnowledge] / [AnsweredUsedKnowledge]) * (1440 * 4)
> >>>>>
> >>>>> This was working great until there was a survey that didn't receive any 
> >>>>> responses and now I receive a Runtime error 6, Overflow.
> >>>>>
> >>>>> I'm not a programmer so I really don't know how to handle this error.  I 
> >>>>> tried using the No Data event but since there is one line indicating the 
> >>>>> survey was sent there really is data.  Then I tried using the On Error event 
> >>>>> to send a message but that didn't work either.  Does anyone have any 
> >>>>> suggestions on how I can fix this?  Thank you in advance.
> >> .
> >>
> .
> 
0
Utf
5/25/2010 12:55:01 PM
Reply:

Similar Artilces:

error 0x80242006
Got error 0x80242006 when trying to d/l kb978207 given to me by winupd.. xp pro sp2 ie7 Tried to install it manually, but says this is for ie6. title of the update claims it is for ie7 on xp. Any ideas? thanks sue Why are you only attempting to install KB978207 now, nearly two months after it was released? Is this the installer you'd downloaded for manual installation? => http://www.microsoft.com/downloads/details.aspx?FamilyID=3510c7d8-7e8f-479e-b6f9-5745a845664d Why hasn't WinXP SP3 been installed by now? Extended Support for WinXP (x86) SP2 ends...

Outlook closes with an error...
Hi, My outlook closes with "outlook.exe has generated errors and will be shut down" error when I click on the drop down arrow for the contact finder. I think it has to do with a windows update for a IE vunerability but am not sure. Please advise..thanks Brijesh ...

IE and Outlook Error
I'm trying to access my work email from home and I get the following error (password works and I get into the Microsoft Exchange Server fine via IE): "Unable to get renderer". Any ideas? The systems guys at work have been working the issue for 2 weeks now and have got nothing. I have been accessing my work email from home for over a year now with no problems. ...

Receiving c1041724 error on restore
I ran the eseutil /cc command against the files in the c:\temp I have pointed it to c:\temp where the restore.env file is located but I am still not able to mount the database. I also have restored from tape the transaction logs. The transaction logs are in e:\exchsrvr\mdbdata. The checkpoint file is in e:\exchsrvr\mdbdata. Does anyone have any ideas? -- Kami Groom MCSE 2000, A+, CNA You have to specify the full path to the ENV file and if you are using the defaults, you have to put quotes like: eseutil /cc "C:\temp\First Storage Group" FD "Kami Groom" <kgroom...

Disable workgroup permissions-Error 3033
Hi, This is the scenario.I copied the database from our branch which has already been closed down.The guy who created the database used some workgroup permissions.Now when I try to open it on my PC i get the error 3033, I do not have the necesarry permissions to open the .mdb.What do I do?I need all the objects in the database.I do not use workgroup permissions on the DBs I create.Please assist. Note: I'm using access 2003 and Windows XP On 30 May 2007 07:02:11 -0700, niclive@gmail.com wrote: >Hi, This is the scenario.I copied the database from our branch which >has already been c...

timeout error sending attachments
Using Outlook Express 6 I can send mail I can recieve mail. I can recieve attachments. I cannot send any attachment no madder the size. I do have Macafee did disable it had no affect. I am on a wireless network but the other computer does not have this problem. Please advise thanks, Dustin ...

Error message 3167
I have a search field for surname in a form using a combo box. When I try to use this I get the message "Record is deleted". Help displays error message 3167. Sorting on the surname field doesn't work - it returns the "record is deleted" message. Hi Greg I don't really understand the problem - you could post more details. Try this. Set the row source of the combo like this (change the name of course) SELECT [TableName].[IDField], [TableName].[Surname], [TableName].[SomeOtherField], [TableName].[AndAnOtherField] FROM [TableName] ORDER BY [Surname], [SomeOt...

Error: Too many different cell formats
Hi, Today I got a strange error with my workbook. When I opened my workbook in excel 2007 I lost all of my cell formats. When I opened the same workbook using Excel 2003 I got an exception with message "Too many different cell formats". I realized that the exception will be thrown if the workbook has more than 4000 unique cell formats. But I am sure I don't have those many cell formats in my workbook. I really need to get my cell format back. Is there any way to get the cell/sheet format? I don't mind if I lose some of the cell formats during my recovery process. Any h...

Text Overflow
Trying to have text on page 1 overflow to page 2. Presently, at bottom of page 1 there's an A with 3 dots (A...). I go into Create Overflow Text Box but can't get the handle to move from page 1 to page 2. Any ideas? In Publisher 2000; On page two create a text box (click the A on the side and draw a rectangle). Go to page 1 and click on the text. This this will turn on the text box. Now click on the chain link at the top of the page and the mouse pointer will change to a pitcher. Go to page two and the mouse pointer pitcher will tilt. Click and the two pages are linked and all t...

Color Scheme Error In Publisher
When I open up publisher 2007, and click on anny of the publication types, this message pops up: the color scheme registry key is either missing or is corrupt. Run setup again to reinstall the color shceme registry. I have reinstalled the program several times and nothing has worked. Does anyone know how I can repair this problem?? I know absolutely nothing about computers.... Try an Office Diagnostic under Help. Have you created any custom color schemes? Do they work? If you have created custom schemes, delete them, see if that helps. They are called *custcols.scm* They are in a...

Why there is no linking error ?
I have a global variable defined as "char toAscii[255]" in some MyFile.c I declared another variable as "extern char *toAscii" in some other file YourFile.c of same console application. In YourFile.c, I accessed toAscii as "toAscii[0] = 1;" Till now there is no linking error. Linker generate same mangaled name for both as "char * toAscii" (?toAscii@@3PADA) but at runtime "toAscii[0] = 1" is causing crash because it point to a NULL while debugger showing a valid address. If I change "extern char *toAscii" to ""extern c...

Business Portal 2.5 Install Error
Someone posted this a while back.. "thebigo9" <thebigo9@discussions.microsoft.com> wrote in message news:491CA957-2C6C-4440-BFD6-CACE94C06584@microsoft.com... > Receive error: The Business Portal data in the database you selected is > not > compatible with this version of the Business Portal. You must select a > different database before continuing We get the same exact thing. Can someone tell me how to get around this error? We are on GP 8.0. Trying to install portal on a new Windows 2003 SBS. You need to check to DB_Upgrade table and check to see all da...

stack overflow problem
Hi, I have traced my problem to the following line of code which seems to be causing a stack overflow problem: (see below) I'm declaring a multidimenstional array of CGridbox Objcts A CGridbox consists of 2 arrays of CApect Objects.. The code works fine when line is commented out. and breaks when put back in... It appears that the object has problems upon creation.... the header file for the delcaration looks like: ==Any ideas what I'm doing wrong??? Thanks very much in advance.... Tony C. ================================================ //CL...

Outlook and Pst error
I was wondering if anyone has seen this error before. "Can’t open this item. An error occurred that prevented the file \\path\*.pst from being saved. Close and then restart all mail-enabled applications." I am using Outlook 2002 on XP/Pro. Any help is appreciated. Thank you. Where does your PST reside? Placing PST files on network drives is not supported. http://support.microsoft.com/default.aspx?scid=kb;en-us;297019 -- Russ Valentine [MVP-Outlook] "brow" <brow@discussions.microsoft.com> wrote in message news:7A0C243C-2DB6-46D2-A2AD-FAEF45995912@micros...

OutputTo Error in A2007
I have a function on my Ribbon to send the active report to Snapshot. DoCmd.OutputTo acOutputReport, "", "SnapshotFormat(*.snp)", "", False, "", 0, _ acExportQualityPrint This works fine when I open with ShiftKey to test, but returns an error when opening the DB without the shifkey. Error 2487 - The object type argument for the action is blank or invalid. Any suggestions will be appreciated. Resolved. it seems A2007 will not output the active report when the report name is left balnk, like earlier versions. I used Screen.ActiveReport.Name ...

error sending email
We configured a dial account for Compuserve Classic using pop.compuserve.com and smtp.compuserve.com We can receive messages, but we can't send them. We are seeing error number 0x800CCC78, as follows: The message could not be sent because the server rejected the sender's e-mail address. The sender's e-mail address was 'dfholden@compuserve.com'. Subject 'den', Account: 'CompuServe', Server: 'smtp.compuserve.com', Protocol: SMTP, Server Response: '554 5.1.3 Denied', Port: 25, Secure(SSL): No, Server Error: 554, Error Number: 0x800CCC...

CRM 3.0 authentication error using web interface
We are running the below configuration: CRM 3.0 on Windows 2003 Server SP1 SQL 2000 Database on separate server Reporting services 2005 on CRM server c360 addon for duplicate detection Our problem began today. Any newly added user gets the the following error when using the web interface. "Authentication Error: Microsoft CRM could not log you on to the system. Make sure your user record is enabled and that you have been assigned at least one security role. For more information, contact your system administrator." The part that makes it difficult is that if I keep randomly try...

Send and Receive All Error Message
When Sending and Receiving before going offline with Outlook XP,(exchange 2000) I get the following error... Task 'Microsoft Exchange Server' reported error (0x8004010F) : 'The operation failed. An object could not be found. ...

How to get text out of overflow in Publisher?
I pasted text from MSWord into a publication, then started adding my photos, but the photos have squeezed all the text into "overflow" and I can't work out how to extend the text boxes to allow all the text enough room to show again. I tried adding pages, but to no avail. GlenorchyGirl wrote: > I pasted text from MSWord into a publication, then started adding my > photos, but the photos have squeezed all the text into "overflow" and > I can't work out how to extend the text boxes to allow all the text > enough room to show again. I tried adding pages...

VB Error Object Library Not Registered
A few days ago a user hard a problem working with a Word XP document that included embedded Excel XP spreadsheets. Since then when she opens Excel spreadsheets that include VB they generate a VB error that says "object library not registered". I did some troubleshooting and found that the error does not occur logged on to the same PC as Administrator and the error does not occur if I add the user to the local Administrators group. The error also does not occur if the user logs onto another computer. I could likely resolve this error by reimaging the PC, but I'd like to find a l...

Ex2k7 Mailbox move error..
I used the command shell to move my mailbox from one MB server to another, same site/org/ip subnet, etc. It seemed to be taking an awful long time and i wasn't seeing any progress. Finally i gave up and ctrl+break'd out of the shell. Now i can't open my mailbox at all. If i try using OWA i get this error: "Log in failed because the mailbox you tried to access is being moved. The mailbox cannot be accessed until this operation is complete. Please wait and try again later." If i try using standard Outlook i get this error: "Cannot start Microsoft Office O...

internal error prevents start up of money
I had money 99 and thought that it was about time that I leap forward six years by upgrading to Money 2005. I searched for a good price and now that after I have installed it, and it has removed my old version, when I try to run it, it shuts down with a message that it has encountered an internal error. I did not find much on the help page. I called the help center and seems like that will get back to me some time. I thought I post a message here to see if someone has a helpful suggestion. Thanks. Syed. Can you post the exact error you see. Also, try the sample file - does that fail w...

Sending error #4
I'm getting a sending reported error 0x800ccc69 The tricky thing is that there is nothing in my outbox. What is it trying to send? Make sure your are running the latest virus protection and spyware removal program. >-----Original Message----- >I'm getting a sending reported error 0x800ccc69 > >The tricky thing is that there is nothing in my outbox. >What is it trying to send? >. > ...

Error Messages #2
Hi I hope you can help... I am using vlookup functions and as we know if it can't find a value i returns a cell value of #N/A. However the result of the lookup is used to calculate another part o the worksheet. So if there is an error #N/A I want it to appear in th spreadsheet either as a blank cell or as a value of 0. Is this possible -- Message posted from http://www.ExcelForum.com Hi try =IF(ISNA(VLOOKUP(....),0,VLOOKUP(...)) HTH Frank > Hi I hope you can help... > > I am using vlookup functions and as we know if it can't find a value > it returns a cell value...

Report Services Error on Client -- but reports work on the server
When I try to run reports on a client a get reporting error. But when I am on Remote Desktop to the CRM server, all reports work. Reporting Services Error -------------------------------------------------------------------------------- An error has occurred during report processing. (rsProcessingAborted) Get Online Help Cannot create a connection to data source 'CRM'. (rsErrorOpeningConnection) Get Online Help Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Note: SQL Report Services is installed on a different server than SQL...