Overflow Error

  • Thread starter Thread starter Ann
  • Start date Start date
A

Ann

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.
 
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))
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
I tried the following and received an error:

=IIF(Count([AnsweredUsedKnowledge]) = 0,0,
[NeverUsedKnowledge] /
IIF(Count([AnsweredUsedKnowledge])=0,1,Count(Count([AnsweredUsedKnowledge]))))
Click to expand...

"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.
Click to expand...

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 said:
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
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 said:
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


:

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.
Click to expand...
Click to expand...
.
Click to expand...
 
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
I tried the following and received an error:

=IIF(Count([AnsweredUsedKnowledge]) = 0,0,
[NeverUsedKnowledge] /
IIF(Count([AnsweredUsedKnowledge])=0,1,Count(Count([AnsweredUsedKnowledge]))))
Click to expand...

"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.
Click to expand...

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 said:
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
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)


:

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


:

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.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top