reporting even though a calculation made is dividing by zero

[Michele E]

I have a report which initially opens a form to input a start and end date in
for the report information. One of the calculated reports items is a % of bad
rolls produced. My report opens; I input the dates and it then runs a query
based on those dates. It takes the data (put into another form) with fields
of "total rolls produced" and "off quality rolls" and divides the "off
quality" by the "total" to give a % bad value. The report works great IF the
"total rolls produced" is not zero. If we do not produce any rolls that day
(due to maintenance problems, etc...) then the report will not open. I just
get the query results with ERR in that field.

I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]), but this still would not run the report if
zeros are in the "total rolls produced field". Do you know how I can have the
report still run, but just have a blank or even ERR on the report to come up?

Thanks.
Michele E

 

[Duane Hookom]

Apparently the issue is that the report returns no records. If this is the
case, try:
=IIF(HasData, [off quality rolls]/[total roll produced],0)
Make sure you spell "off" correctly.

Keep in mind that a report that doesn't return any records will not render
any detail sections.

If this isn't your issue, please post back.

Duane Hookom
MS Access MVP

 

[Al Campagna]

Michele,
If I might add a comment in this thread...
Whenever you place code in a question, or reply, in a newsgroup post,
always Cut & Paste exactly the code you have. Don't rely on hand
typing, as you may introduce additional problems other than the one your
post
is intended to deal with.

of "total rolls produced" and "off quality rolls" and
I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]),

There's two mistakes in your typing...
In one instance you refer to [total rolls produced] and in the
calculation you refer to [total roll produced].
In one instance you refer to "off quality rolls" and in the
calculation
you refer to [of quality rolls].

Had you used the above code, you would have received a #name error,
well before the divide by 0 issue.
Always Cut & Paste your code in your posts...
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have a report which initially opens a form to input a start and end date
in
for the report information. One of the calculated reports items is a % of
bad
rolls produced. My report opens; I input the dates and it then runs a
query
based on those dates. It takes the data (put into another form) with
fields
of "total rolls produced" and "off quality rolls" and divides the "off
quality" by the "total" to give a % bad value. The report works great IF
the
"total rolls produced" is not zero. If we do not produce any rolls that
day
(due to maintenance problems, etc...) then the report will not open. I
just
get the query results with ERR in that field.

I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]), but this still would not run the report if
zeros are in the "total rolls produced field". Do you know how I can have
the
report still run, but just have a blank or even ERR on the report to come
up?

Thanks.
Michele E

 

[Michele E]

Thank you. I actually did cut and paste in my databse, however, I made the
typo here in my explanation! I will try the "has data" string and see if that
works and let you know.
--
Michele E

Michele,
If I might add a comment in this thread...
Whenever you place code in a question, or reply, in a newsgroup post,
always Cut & Paste exactly the code you have. Don't rely on hand
typing, as you may introduce additional problems other than the one your
post
is intended to deal with.

of "total rolls produced" and "off quality rolls" and
I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]),

There's two mistakes in your typing...
In one instance you refer to [total rolls produced] and in the
calculation you refer to [total roll produced].
In one instance you refer to "off quality rolls" and in the
calculation
you refer to [of quality rolls].

Had you used the above code, you would have received a #name error,
well before the divide by 0 issue.
Always Cut & Paste your code in your posts...
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have a report which initially opens a form to input a start and end date
in
for the report information. One of the calculated reports items is a % of
bad
rolls produced. My report opens; I input the dates and it then runs a
query
based on those dates. It takes the data (put into another form) with
fields
of "total rolls produced" and "off quality rolls" and divides the "off
quality" by the "total" to give a % bad value. The report works great IF
the
"total rolls produced" is not zero. If we do not produce any rolls that
day
(due to maintenance problems, etc...) then the report will not open. I
just
get the query results with ERR in that field.

I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]), but this still would not run the report if
zeros are in the "total rolls produced field". Do you know how I can have
the
report still run, but just have a blank or even ERR on the report to come
up?

Thanks.
Michele E

.

 

[Al Campagna]

Yes... Duane's response should do the trick.
Al

Thank you. I actually did cut and paste in my databse, however, I made the
typo here in my explanation! I will try the "has data" string and see if
that
works and let you know.
--
Michele E

Michele,
If I might add a comment in this thread...
Whenever you place code in a question, or reply, in a newsgroup post,
always Cut & Paste exactly the code you have. Don't rely on hand
typing, as you may introduce additional problems other than the one your
post
is intended to deal with.

of "total rolls produced" and "off quality rolls" and
I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]),

There's two mistakes in your typing...
In one instance you refer to [total rolls produced] and in the
calculation you refer to [total roll produced].
In one instance you refer to "off quality rolls" and in the
calculation
you refer to [of quality rolls].

Had you used the above code, you would have received a #name error,
well before the divide by 0 issue.
Always Cut & Paste your code in your posts...
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have a report which initially opens a form to input a start and end
date
in
for the report information. One of the calculated reports items is a %
of
bad
rolls produced. My report opens; I input the dates and it then runs a
query
based on those dates. It takes the data (put into another form) with
fields
of "total rolls produced" and "off quality rolls" and divides the "off
quality" by the "total" to give a % bad value. The report works great
IF
the
"total rolls produced" is not zero. If we do not produce any rolls that
day
(due to maintenance problems, etc...) then the report will not open. I
just
get the query results with ERR in that field.

I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]), but this still would not run the report
if
zeros are in the "total rolls produced field". Do you know how I can
have
the
report still run, but just have a blank or even ERR on the report to
come
up?

Thanks.
Michele E

.

 

[Michele E]

Hi Duane,

This did not work. I put it in the Properties section for the calculated
field in my report, but if I chose a date that I knew had blanks (or zeros -
I tried both) in the [total rolls produced] field, the report would not open.
The query opens with an error message stating "This expression is typed
incorrectly, or it is too complex to be evaluated..."
When I chose a date with 1 (or more) [total rolls produced], the report
opened up with no query error.

Any more suggestions?
--
Michele E

Apparently the issue is that the report returns no records. If this is the
case, try:
=IIF(HasData, [off quality rolls]/[total roll produced],0)
Make sure you spell "off" correctly.

Keep in mind that a report that doesn't return any records will not render
any detail sections.

If this isn't your issue, please post back.

Duane Hookom
MS Access MVP

I have a report which initially opens a form to input a start and end date
in
for the report information. One of the calculated reports items is a % of
bad
rolls produced. My report opens; I input the dates and it then runs a
query
based on those dates. It takes the data (put into another form) with
fields
of "total rolls produced" and "off quality rolls" and divides the "off
quality" by the "total" to give a % bad value. The report works great IF
the
"total rolls produced" is not zero. If we do not produce any rolls that
day
(due to maintenance problems, etc...) then the report will not open. I
just
get the query results with ERR in that field.

I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]), but this still would not run the report if
zeros are in the "total rolls produced field". Do you know how I can have
the
report still run, but just have a blank or even ERR on the report to come
up?

Thanks.
Michele E

 

[Michele E]

Al,

I should have copied you on my response back to Duane.
This did not work. I put it in the Properties section for the calculated
field in my report, but if I chose a date that I knew had blanks (or zeros -
I tried both) in the [total rolls produced] field, the report would not open.
The query opens with an error message stating "This expression is typed
incorrectly, or it is too complex to be evaluated..."
When I chose a date with 1 (or more) [total rolls produced], the report
opened up with no query error.

Any more suggestions?
--
Michele E



--
Michele E

Yes... Duane's response should do the trick.
Al

Thank you. I actually did cut and paste in my databse, however, I made the
typo here in my explanation! I will try the "has data" string and see if
that
works and let you know.
--
Michele E

Michele,
If I might add a comment in this thread...
Whenever you place code in a question, or reply, in a newsgroup post,
always Cut & Paste exactly the code you have. Don't rely on hand
typing, as you may introduce additional problems other than the one your
post
is intended to deal with.

You wrote:
of "total rolls produced" and "off quality rolls"
and
I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]),

There's two mistakes in your typing...
In one instance you refer to [total rolls produced] and in the
calculation you refer to [total roll produced].
In one instance you refer to "off quality rolls" and in the
calculation
you refer to [of quality rolls].

Had you used the above code, you would have received a #name error,
well before the divide by 0 issue.
Always Cut & Paste your code in your posts...
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."





I have a report which initially opens a form to input a start and end
date
in
for the report information. One of the calculated reports items is a %
of
bad
rolls produced. My report opens; I input the dates and it then runs a
query
based on those dates. It takes the data (put into another form) with
fields
of "total rolls produced" and "off quality rolls" and divides the "off
quality" by the "total" to give a % bad value. The report works great
IF
the
"total rolls produced" is not zero. If we do not produce any rolls that
day
(due to maintenance problems, etc...) then the report will not open. I
just
get the query results with ERR in that field.

I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]), but this still would not run the report
if
zeros are in the "total rolls produced field". Do you know how I can
have
the
report still run, but just have a blank or even ERR on the report to
come
up?

Thanks.
Michele E


.

.

 

[Duane Hookom]

What did you put where? The expression I suggested should have been entered
into a control source property.

Can you explain what you mean by "a date that I knew had blanks"? What are
"blanks"? Is this no values in particular fields or no records returned in
your report or what?

I assume you are seeing errors some place but don't know if these are popped
up in a message box or returned in controls or what.

Duane Hookom
MS Access MVP

Al,

I should have copied you on my response back to Duane.
This did not work. I put it in the Properties section for the calculated
field in my report, but if I chose a date that I knew had blanks (or
zeros -
I tried both) in the [total rolls produced] field, the report would not
open.
The query opens with an error message stating "This expression is typed
incorrectly, or it is too complex to be evaluated..."
When I chose a date with 1 (or more) [total rolls produced], the report
opened up with no query error.

Any more suggestions?
--
Michele E



--
Michele E

Yes... Duane's response should do the trick.
Al

Thank you. I actually did cut and paste in my databse, however, I made
the
typo here in my explanation! I will try the "has data" string and see
if
that
works and let you know.
--
Michele E


:

Michele,
If I might add a comment in this thread...
Whenever you place code in a question, or reply, in a newsgroup
post,
always Cut & Paste exactly the code you have. Don't rely on hand
typing, as you may introduce additional problems other than the one
your
post
is intended to deal with.

You wrote:
of "total rolls produced" and "off quality rolls"
and
I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]),

There's two mistakes in your typing...
In one instance you refer to [total rolls produced] and in the
calculation you refer to [total roll produced].
In one instance you refer to "off quality rolls" and in the
calculation
you refer to [of quality rolls].

Had you used the above code, you would have received a #name
error,
well before the divide by 0 issue.
Always Cut & Paste your code in your posts...
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."





I have a report which initially opens a form to input a start and end
date
in
for the report information. One of the calculated reports items is a
%
of
bad
rolls produced. My report opens; I input the dates and it then runs
a
query
based on those dates. It takes the data (put into another form) with
fields
of "total rolls produced" and "off quality rolls" and divides the
"off
quality" by the "total" to give a % bad value. The report works
great
IF
the
"total rolls produced" is not zero. If we do not produce any rolls
that
day
(due to maintenance problems, etc...) then the report will not open.
I
just
get the query results with ERR in that field.

I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]), but this still would not run the
report
if
zeros are in the "total rolls produced field". Do you know how I can
have
the
report still run, but just have a blank or even ERR on the report to
come
up?

Thanks.
Michele E


.

.

 

[Michele E]

Duane,

I did put the expression in the control source property of the field [% bad]
that is in my report.
What I meant by the "date with blanks" is: I enter a start date and end date
(to create my report for that period of time). There are days in our process
when we do not have any production, so the [total rolls produced] = 0. If I
choose a date range which has days with no production the report will not
open because of the errors in the query. When I choose a date with days that
all have [total rolls produced] >0, then the report works.

The error "This expression is typed incorrectly, or it is too complex to be
evaluated..." pops up in a message box just over the query that runs to get
the report information. The report does not open up though. In the query, ERR
is in the fields for [% bad] for dates in which there were no [total rolls
produced].

Does that make sense?
--
Michele E

What did you put where? The expression I suggested should have been entered
into a control source property.

Can you explain what you mean by "a date that I knew had blanks"? What are
"blanks"? Is this no values in particular fields or no records returned in
your report or what?

I assume you are seeing errors some place but don't know if these are popped
up in a message box or returned in controls or what.

Duane Hookom
MS Access MVP

Al,

I should have copied you on my response back to Duane.
This did not work. I put it in the Properties section for the calculated
field in my report, but if I chose a date that I knew had blanks (or
zeros -
I tried both) in the [total rolls produced] field, the report would not
open.
The query opens with an error message stating "This expression is typed
incorrectly, or it is too complex to be evaluated..."
When I chose a date with 1 (or more) [total rolls produced], the report
opened up with no query error.

Any more suggestions?
--
Michele E



--
Michele E

Yes... Duane's response should do the trick.
Al

Thank you. I actually did cut and paste in my databse, however, I made
the
typo here in my explanation! I will try the "has data" string and see
if
that
works and let you know.
--
Michele E


:

Michele,
If I might add a comment in this thread...
Whenever you place code in a question, or reply, in a newsgroup
post,
always Cut & Paste exactly the code you have. Don't rely on hand
typing, as you may introduce additional problems other than the one
your
post
is intended to deal with.

You wrote:
of "total rolls produced" and "off quality rolls"
and
I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]),

There's two mistakes in your typing...
In one instance you refer to [total rolls produced] and in the
calculation you refer to [total roll produced].
In one instance you refer to "off quality rolls" and in the
calculation
you refer to [of quality rolls].

Had you used the above code, you would have received a #name
error,
well before the divide by 0 issue.
Always Cut & Paste your code in your posts...
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."





I have a report which initially opens a form to input a start and end
date
in
for the report information. One of the calculated reports items is a
%
of
bad
rolls produced. My report opens; I input the dates and it then runs
a
query
based on those dates. It takes the data (put into another form) with
fields
of "total rolls produced" and "off quality rolls" and divides the
"off
quality" by the "total" to give a % bad value. The report works
great
IF
the
"total rolls produced" is not zero. If we do not produce any rolls
that
day
(due to maintenance problems, etc...) then the report will not open.
I
just
get the query results with ERR in that field.

I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]), but this still would not run the
report
if
zeros are in the "total rolls produced field". Do you know how I can
have
the
report still run, but just have a blank or even ERR on the report to
come
up?

Thanks.
Michele E


.



.

 

[Duane Hookom]

"because of the errors in the query" what is the SQL of the query?
Typically a query that doesn't return records to a report will not keep the
report from opening. You might see #error in some control sources unless you
fix them like I suggested.

If your query has a divide by zero error then you need to fix the query, not
the report.

--
Duane Hookom
Microsoft Access MVP

Duane,

I did put the expression in the control source property of the field [% bad]
that is in my report.
What I meant by the "date with blanks" is: I enter a start date and end date
(to create my report for that period of time). There are days in our process
when we do not have any production, so the [total rolls produced] = 0. If I
choose a date range which has days with no production the report will not
open because of the errors in the query. When I choose a date with days that
all have [total rolls produced] >0, then the report works.

The error "This expression is typed incorrectly, or it is too complex to be
evaluated..." pops up in a message box just over the query that runs to get
the report information. The report does not open up though. In the query, ERR
is in the fields for [% bad] for dates in which there were no [total rolls
produced].

Does that make sense?
--
Michele E

What did you put where? The expression I suggested should have been entered
into a control source property.

Can you explain what you mean by "a date that I knew had blanks"? What are
"blanks"? Is this no values in particular fields or no records returned in
your report or what?

I assume you are seeing errors some place but don't know if these are popped
up in a message box or returned in controls or what.

Duane Hookom
MS Access MVP

Al,

I should have copied you on my response back to Duane.
This did not work. I put it in the Properties section for the calculated
field in my report, but if I chose a date that I knew had blanks (or
zeros -
I tried both) in the [total rolls produced] field, the report would not
open.
The query opens with an error message stating "This expression is typed
incorrectly, or it is too complex to be evaluated..."
When I chose a date with 1 (or more) [total rolls produced], the report
opened up with no query error.

Any more suggestions?
--
Michele E



--
Michele E


:

Yes... Duane's response should do the trick.
Al

Thank you. I actually did cut and paste in my databse, however, I made
the
typo here in my explanation! I will try the "has data" string and see
if
that
works and let you know.
--
Michele E


:

Michele,
If I might add a comment in this thread...
Whenever you place code in a question, or reply, in a newsgroup
post,
always Cut & Paste exactly the code you have. Don't rely on hand
typing, as you may introduce additional problems other than the one
your
post
is intended to deal with.

You wrote:
of "total rolls produced" and "off quality rolls"
and
I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]),

There's two mistakes in your typing...
In one instance you refer to [total rolls produced] and in the
calculation you refer to [total roll produced].
In one instance you refer to "off quality rolls" and in the
calculation
you refer to [of quality rolls].

Had you used the above code, you would have received a #name
error,
well before the divide by 0 issue.
Always Cut & Paste your code in your posts...
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."





I have a report which initially opens a form to input a start and end
date
in
for the report information. One of the calculated reports items is a
%
of
bad
rolls produced. My report opens; I input the dates and it then runs
a
query
based on those dates. It takes the data (put into another form) with
fields
of "total rolls produced" and "off quality rolls" and divides the
"off
quality" by the "total" to give a % bad value. The report works
great
IF
the
"total rolls produced" is not zero. If we do not produce any rolls
that
day
(due to maintenance problems, etc...) then the report will not open.
I
just
get the query results with ERR in that field.

I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]), but this still would not run the
report
if
zeros are in the "total rolls produced field". Do you know how I can
have
the
report still run, but just have a blank or even ERR on the report to
come
up?

Thanks.
Michele E


.



.

 

[Michele E]

Duane,

This is what the SQL says for this calculation:
[Total Setout Rolls], Nz([Total Setout Rolls])/Nz([Rolls Produced]) AS
SetoutPercentage

where
Total Setout Rolls = off quality rolls
Rolls Produced = total rolls produced
SetoutPercentage = % bad
from initial email.
--
Michele E

"because of the errors in the query" what is the SQL of the query?
Typically a query that doesn't return records to a report will not keep the
report from opening. You might see #error in some control sources unless you
fix them like I suggested.

If your query has a divide by zero error then you need to fix the query, not
the report.

--
Duane Hookom
Microsoft Access MVP

Duane,

I did put the expression in the control source property of the field [% bad]
that is in my report.
What I meant by the "date with blanks" is: I enter a start date and end date
(to create my report for that period of time). There are days in our process
when we do not have any production, so the [total rolls produced] = 0. If I
choose a date range which has days with no production the report will not
open because of the errors in the query. When I choose a date with days that
all have [total rolls produced] >0, then the report works.

The error "This expression is typed incorrectly, or it is too complex to be
evaluated..." pops up in a message box just over the query that runs to get
the report information. The report does not open up though. In the query, ERR
is in the fields for [% bad] for dates in which there were no [total rolls
produced].

Does that make sense?
--
Michele E

What did you put where? The expression I suggested should have been entered
into a control source property.

Can you explain what you mean by "a date that I knew had blanks"? What are
"blanks"? Is this no values in particular fields or no records returned in
your report or what?

I assume you are seeing errors some place but don't know if these are popped
up in a message box or returned in controls or what.

Duane Hookom
MS Access MVP

Al,

I should have copied you on my response back to Duane.
This did not work. I put it in the Properties section for the calculated
field in my report, but if I chose a date that I knew had blanks (or
zeros -
I tried both) in the [total rolls produced] field, the report would not
open.
The query opens with an error message stating "This expression is typed
incorrectly, or it is too complex to be evaluated..."
When I chose a date with 1 (or more) [total rolls produced], the report
opened up with no query error.

Any more suggestions?
--
Michele E



--
Michele E


:

Yes... Duane's response should do the trick.
Al

Thank you. I actually did cut and paste in my databse, however, I made
the
typo here in my explanation! I will try the "has data" string and see
if
that
works and let you know.
--
Michele E


:

Michele,
If I might add a comment in this thread...
Whenever you place code in a question, or reply, in a newsgroup
post,
always Cut & Paste exactly the code you have. Don't rely on hand
typing, as you may introduce additional problems other than the one
your
post
is intended to deal with.

You wrote:
of "total rolls produced" and "off quality rolls"
and
I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]),

There's two mistakes in your typing...
In one instance you refer to [total rolls produced] and in the
calculation you refer to [total roll produced].
In one instance you refer to "off quality rolls" and in the
calculation
you refer to [of quality rolls].

Had you used the above code, you would have received a #name
error,
well before the divide by 0 issue.
Always Cut & Paste your code in your posts...
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."





I have a report which initially opens a form to input a start and end
date
in
for the report information. One of the calculated reports items is a
%
of
bad
rolls produced. My report opens; I input the dates and it then runs
a
query
based on those dates. It takes the data (put into another form) with
fields
of "total rolls produced" and "off quality rolls" and divides the
"off
quality" by the "total" to give a % bad value. The report works
great
IF
the
"total rolls produced" is not zero. If we do not produce any rolls
that
day
(due to maintenance problems, etc...) then the report will not open.
I
just
get the query results with ERR in that field.

I tried to use the IIF([total rolls produced]=0,Null,[of quality
rolls]/[total roll produced]), but this still would not run the
report
if
zeros are in the "total rolls produced field". Do you know how I can
have
the
report still run, but just have a blank or even ERR on the report to
come
up?

Thanks.
Michele E


.



.

 

[Michele E]

Thank you, Ken.

I'm new to Access, so I don't know wll the lingo. You are correct in what I
am trying to say though. I will try the expression you have below, however,
there are times when [Total Setout Rolls] does equal zero, but the [Rolls
Produced] does not. It just means that all the rolls we made that day were
good quality. Will this expression still work?

The report does load when the data for the time period contains [Rolls
Produced] >0. Do you know why that is (if it is not a division by zero error)?
--
Michele E

Michele:

If Rolls Produced is Null or zero, will Total Setout Rolls inevitably be Null
or zero also? It seems logical to me that this should be the case as if no
rolls are produced none can be off quality. If so you can avoid the division
by zero by dividing zero by one:

Nz([Total Setout Rolls],0)/Nz([Rolls Produced],1) AS SetoutPercentage

which results in zero of course.

While that should eliminate the error in the query, I'm not convinced that
this per se is the source of the error which prevents the report from loading.
That would only normally happen if the error results from some expression in
the report itself. If this was a 'division by zero' error then that is the
error message which would be expected, but you are getting a more generic
message, which might mean there is some other expression in the report, most
likely as a control's ControlSource property, which is preventing the report
from loading. Its possible this may ultimately stem from the 'division by
zero' error in the query, and eliminating that will cure it, but it may well
be something else.

I'm also confused by the references in your description to the query being
'run' and the report being opened, which suggest that both the query and
report are being opened (the former as a datasheet?). Normally the query
would simply serve behind the scenes as the report's RecordSource property,
and only the report would be opened from the dialogue form in which the date
range is entered.

Ken Sheridan
Stafford, England

Duane,

This is what the SQL says for this calculation:
[Total Setout Rolls], Nz([Total Setout Rolls])/Nz([Rolls Produced]) AS
SetoutPercentage

where
Total Setout Rolls = off quality rolls
Rolls Produced = total rolls produced
SetoutPercentage = % bad
from initial email.

"because of the errors in the query" what is the SQL of the query?
Typically a query that doesn't return records to a report will not keep the

[quoted text clipped - 131 lines]

.

 

[Michele E]

Ken,

I tried the expression and it came back with a syntex error (comma). I put
it in exactly like you have it written, but it was still incorrect. I put it
in the control source (for that item) of the report. I also tried it on the
query criteria area, but it continued to say the syntex was not right.

Any other suggestions?
--
Michele E

Michele:

A zero Total Setout Rolls value and a non-zero Rolls Produced value is not a
problem with the expression I posted as the result will correctly be zero.
What I was pointing out was that if the Rolls Produced value is zero then
Total Setout Rolls value must, as I see it, also be zero, so dividing zero by
one will again correctly result in zero, avoiding the division by zero error.

If this is the source of the error preventing the report from loading then
this should cure it, but if it doesn't I think you need to look for something
in the report itself rather than in the query which is preventing it loading.
But lets hope that's not the case, and changing the expression in the query
does the trick.

Ken Sheridan
Stafford, England

Thank you, Ken.

I'm new to Access, so I don't know wll the lingo. You are correct in what I
am trying to say though. I will try the expression you have below, however,
there are times when [Total Setout Rolls] does equal zero, but the [Rolls
Produced] does not. It just means that all the rolls we made that day were
good quality. Will this expression still work?

The report does load when the data for the time period contains [Rolls
Produced] >0. Do you know why that is (if it is not a division by zero error)?

Michele:

[quoted text clipped - 44 lines]

.

 

[Michele E]

Hi Ken,

I tried the expression in SQL in the query and then also in the control
source of the report.
My report still will not load when there are machines that produce no rolls
on the date that I'm running the report for. The report loads when all
machines produce rolls for the day.
I am not getting the syntax error now. I am back to getting "This expression
is typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many complicated elements. Try simplifying
the expression by assigning parts of the expression to variables."

What does that mean?
Thanks.
--
Michele E

Michele:

I can't see a syntax error in the expression myself. I posted it as it would
be in the query in SQL view. In query design view it would be like this in
the 'field' row of column:

SetoutPercentage:Nz([Total Setout Rolls],0)/Nz([Rolls Produced],1)

As the ControlSource property of a text box in a report:

=Nz([Total Setout Rolls],0)/Nz([Rolls Produced],1)

Ken Sheridan
Stafford, England

Ken,

I tried the expression and it came back with a syntex error (comma). I put
it in exactly like you have it written, but it was still incorrect. I put it
in the control source (for that item) of the report. I also tried it on the
query criteria area, but it continued to say the syntex was not right.

Any other suggestions?

Michele:

[quoted text clipped - 28 lines]

.

 

[Michele E]

Thank you, Ken.

I recreated the report and made an adjustment to a calculated field (average
SetoutPercentage) and it works now.
I appreciate all of your help. I've learned quite a bit about Access over
the last few days.
--
Michele E

Michele:

I don't think we are going to be able to put our finger on this without some
hands on debugging. I suspect there is something in the report definition
which is causing the problem, but I can't say what that might be.

From your descriptions I'm not totally clear as to the sequence of events
involved here. The scenario I'd envisage would be along these lines:

1. You have a form in which two date values are entered into controls to
restrict a report to records within those dates.

2. On this form there is a button which opens the report by calling the
OpenReport method.

3. The report has as its RecordSource a query which references as parameters
the two control on the form in which the dates are entered.

You've talked in some places about the query being 'run' as though its being
opened independently of the report, but in the scenario as envisaged above
that never happens, and there is no reason why it should as the query's role
is as the report's RecordSource, and it has no need to be 'run' independently.


Once the division by zero error is eliminated by means of the expression
which makes it a legitimate division of zero by one, I can see no reason why
the report should fail to load, so I think there must be something in the
report definition which derives from the fact that there is no production on
a certain date which is rising an error in the loading of the report. I
don't think this can be simply a matter of division by zero as, if that were
the case, the error reported would be to that effect. The error message you
are getting is of a more generic character, which makes the cause more
difficult to pin down. The problem might not even be in the report itself;
it could for instance be in a function called in its module.

As has been said before the thing to concentrate on here is getting the query
to return the correct rows. You can test this by opening the form, entering
the dates and then opening the query directly while the form is open,
forgetting completely about the report for the time being. Once the query is
returning the data correctly creating a report to present the results of the
query should be a simple task. You'll probably find it simpler to create a
completely new report than trying pin down whatever is the problem with the
current one. You'd probably be able to copy and paste all or at least most
of the controls from the current one into a new one, so you won't have to
reinvent the wheel completely.

Ken Sheridan
Stafford, England

Hi Ken,

I tried the expression in SQL in the query and then also in the control
source of the report.
My report still will not load when there are machines that produce no rolls
on the date that I'm running the report for. The report loads when all
machines produce rolls for the day.
I am not getting the syntax error now. I am back to getting "This expression
is typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many complicated elements. Try simplifying
the expression by assigning parts of the expression to variables."

What does that mean?
Thanks.

Michele:

[quoted text clipped - 24 lines]

.

--
Message posted via AccessMonster.com


.