reporting even though a calculation made is dividing by zero

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
0
Utf
2/9/2010 10:36:01 PM
access 16762 articles. 3 followers. Follow

18 Replies
866 Views

Similar Articles

[PageSpeed] 36

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

"Michele E" <MicheleE@discussions.microsoft.com> wrote in message 
news:B08F2F99-F819-4EE3-A5E8-430349FADD88@microsoft.com...
> 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 

0
Duane
2/10/2010 5:35:39 AM
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."





"Michele E" <MicheleE@discussions.microsoft.com> wrote in message 
news:B08F2F99-F819-4EE3-A5E8-430349FADD88@microsoft.com...
>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 


0
Al
2/10/2010 3:43:18 PM
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


"Al Campagna" wrote:

> 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."
> 
> 
> 
> 
> 
> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message 
> news:B08F2F99-F819-4EE3-A5E8-430349FADD88@microsoft.com...
> >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 
> 
> 
> .
> 
0
Utf
2/10/2010 4:44:02 PM
Yes... Duane's response should do the trick.
Al

"Michele E" <MicheleE@discussions.microsoft.com> wrote in message 
news:851BB667-D261-49B6-9224-45C33DAE897A@microsoft.com...
> 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
>
>
> "Al Campagna" wrote:
>
>> 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."
>>
>>
>>
>>
>>
>> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message
>> news:B08F2F99-F819-4EE3-A5E8-430349FADD88@microsoft.com...
>> >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
>>
>>
>> .
>> 


0
Al
2/10/2010 5:01:40 PM
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


"Duane Hookom" wrote:

> 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
> 
> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message 
> news:B08F2F99-F819-4EE3-A5E8-430349FADD88@microsoft.com...
> > 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 
> 
0
Utf
2/10/2010 5:09:02 PM
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


"Al Campagna" wrote:

> Yes... Duane's response should do the trick.
> Al
> 
> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message 
> news:851BB667-D261-49B6-9224-45C33DAE897A@microsoft.com...
> > 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
> >
> >
> > "Al Campagna" wrote:
> >
> >> 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."
> >>
> >>
> >>
> >>
> >>
> >> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message
> >> news:B08F2F99-F819-4EE3-A5E8-430349FADD88@microsoft.com...
> >> >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
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
2/10/2010 9:37:02 PM
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

"Michele E" <MicheleE@discussions.microsoft.com> wrote in message 
news:6D9BEBCF-E2B9-42A4-938B-820385D1E3B3@microsoft.com...
> 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
>
>
> "Al Campagna" wrote:
>
>> Yes... Duane's response should do the trick.
>> Al
>>
>> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message
>> news:851BB667-D261-49B6-9224-45C33DAE897A@microsoft.com...
>> > 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
>> >
>> >
>> > "Al Campagna" wrote:
>> >
>> >> 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."
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message
>> >> news:B08F2F99-F819-4EE3-A5E8-430349FADD88@microsoft.com...
>> >> >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
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>> 
0
Duane
2/11/2010 1:48:15 AM
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


"Duane Hookom" wrote:

> 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
> 
> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message 
> news:6D9BEBCF-E2B9-42A4-938B-820385D1E3B3@microsoft.com...
> > 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
> >
> >
> > "Al Campagna" wrote:
> >
> >> Yes... Duane's response should do the trick.
> >> Al
> >>
> >> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message
> >> news:851BB667-D261-49B6-9224-45C33DAE897A@microsoft.com...
> >> > 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
> >> >
> >> >
> >> > "Al Campagna" wrote:
> >> >
> >> >> 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."
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message
> >> >> news:B08F2F99-F819-4EE3-A5E8-430349FADD88@microsoft.com...
> >> >> >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
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >> 
0
Utf
2/11/2010 4:30:02 PM
"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


"Michele E" wrote:

> 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
> 
> 
> "Duane Hookom" wrote:
> 
> > 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
> > 
> > "Michele E" <MicheleE@discussions.microsoft.com> wrote in message 
> > news:6D9BEBCF-E2B9-42A4-938B-820385D1E3B3@microsoft.com...
> > > 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
> > >
> > >
> > > "Al Campagna" wrote:
> > >
> > >> Yes... Duane's response should do the trick.
> > >> Al
> > >>
> > >> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message
> > >> news:851BB667-D261-49B6-9224-45C33DAE897A@microsoft.com...
> > >> > 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
> > >> >
> > >> >
> > >> > "Al Campagna" wrote:
> > >> >
> > >> >> 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."
> > >> >>
> > >> >>
> > >> >>
> > >> >>
> > >> >>
> > >> >> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message
> > >> >> news:B08F2F99-F819-4EE3-A5E8-430349FADD88@microsoft.com...
> > >> >> >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
> > >> >>
> > >> >>
> > >> >> .
> > >> >>
> > >>
> > >>
> > >> .
> > >> 
0
Utf
2/11/2010 10:00:01 PM
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


"Duane Hookom" wrote:

> "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
> 
> 
> "Michele E" wrote:
> 
> > 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
> > 
> > 
> > "Duane Hookom" wrote:
> > 
> > > 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
> > > 
> > > "Michele E" <MicheleE@discussions.microsoft.com> wrote in message 
> > > news:6D9BEBCF-E2B9-42A4-938B-820385D1E3B3@microsoft.com...
> > > > 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
> > > >
> > > >
> > > > "Al Campagna" wrote:
> > > >
> > > >> Yes... Duane's response should do the trick.
> > > >> Al
> > > >>
> > > >> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message
> > > >> news:851BB667-D261-49B6-9224-45C33DAE897A@microsoft.com...
> > > >> > 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
> > > >> >
> > > >> >
> > > >> > "Al Campagna" wrote:
> > > >> >
> > > >> >> 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."
> > > >> >>
> > > >> >>
> > > >> >>
> > > >> >>
> > > >> >>
> > > >> >> "Michele E" <MicheleE@discussions.microsoft.com> wrote in message
> > > >> >> news:B08F2F99-F819-4EE3-A5E8-430349FADD88@microsoft.com...
> > > >> >> >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
> > > >> >>
> > > >> >>
> > > >> >> .
> > > >> >>
> > > >>
> > > >>
> > > >> .
> > > >> 
0
Utf
2/12/2010 3:58:10 PM
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

Michele E wrote:
>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]
>> > > >>
>> > > >> .

-- 
Message posted via http://www.accessmonster.com

0
KenSheridan
2/12/2010 4:35:58 PM
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


"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> Michele E wrote:
> >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]
> >> > > >>
> >> > > >> .
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
2/12/2010 9:38:01 PM
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

Michele E wrote:
>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]
>> >> > > >>
>> >> > > >> .

-- 
Message posted via http://www.accessmonster.com

0
KenSheridan
2/12/2010 10:15:10 PM
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


"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> Michele E wrote:
> >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]
> >> >> > > >>
> >> >> > > >> .
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
2/15/2010 4:31:01 PM
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

Michele E wrote:
>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]
>> >> >> > > >>
>> >> >> > > >> .

-- 
Message posted via http://www.accessmonster.com

0
KenSheridan
2/15/2010 5:44:55 PM
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


"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> Michele E wrote:
> >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]
> >> >> >> > > >>
> >> >> >> > > >> .
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
2/15/2010 7:51:02 PM
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

Michele E wrote:
>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
http://www.accessmonster.com/Uwe/Forums.aspx/access/201002/1

0
KenSheridan
2/15/2010 11:29:14 PM
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


"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> Michele E wrote:
> >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
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201002/1
> 
> .
> 
0
Utf
2/16/2010 6:46:06 PM
Reply:

Similar Artilces:

Need more Reports
GP does not provide detailed reports itself. reports such as sales by customer, best selling items, inventory valuation and so... Also the reports that it provides are not in good design and format (not useful). Is there any report package from third party that can be purchased or there is any other solution to get in depth reports and analysis? Sure Since Great Plains cannot possibly be all things to all people, get to know the layout of the Great Plains database and where all of the data is stored. Then roll your own reports using the native Report Writer, SQL Reporting Services, Cryst...

Customized CRM Report with Reporting Services
I have written a customized report in Reporting Services using SQL Server 2005. I need to use a date as one of my query parameters. I am using three parameters, one being UserName, 2) Customer, and 3) Date. I want the report to filter on the username, then customer, then date in order to provide a specific report for 1 customer and 1 date only. I changed the select for the date to: Select Distinct Convert (varchar, new_tripdate,101) in order to cut the time out of view. When I run the report, I first select the username, then I am provided with a list of customers for that user, but the...

Sales Report #3
Hi All Is there any sales report to show detailes like Store-total Sales-Cash Sales-Credit-Span-customer- Change Item/customer -Discount. Cash Sales-Credit-Span: is tender type. Change Item/customer : like one customer change item and total amount for backing item 200$ (e.g). Is there any one he have report like that? Thanks for help M. Own ...

W-2 Tax Reporting Figures Not Accurate
I use Money to monitor all of my financial situations. This year, I decided to see how it did with supplying tax information. "Poorly" is the answer. I would expect the 'Tax Line Manager / W-2 / Your salary' and the 'Tax Software Report / W-2 / Your salary' to show a figure that matches my 'W-2 / Box 1' statement information. They do not. The reason is that the 'W-2 / Box 1' statement figure has pre-tax retirement contributions subtracted from the wages. When I enter my paycheck information into Money, I categorize retirement contributions no...

Blank networth reports
Hi, Whenever I run the net worth reports, I get the hourglass for a second or two, then loading but it never renders. Other reports (Transactions, loans, cap gains, etc.) run fine. I converted from Quicken. In microsoft.public.money, <anonymous@discussions.microsoft.com> wrote: >Hi, Whenever I run the net worth reports, I get the >hourglass for a second or two, then loading but it never >renders. Other reports (Transactions, loans, cap gains, >etc.) run fine. Perhaps customize the report to a different time frame, and then reset the report? Tried all dates. >...

eXtender SOP Reports
Hello. We are using a modified version of the eXtender SOP Blank Packing Slip Form. It was modified years ago by an outside consultant. For the first time, we need to use the "Print Customer Item" option, and notice this does not work on our modified report (it works fine on the non-modified version of the same eXtender report). I am being a little lazy here and hoping someone can tell me how to turn this "feature" off or on in modifier :) Thank you, in advance. M. ...

Reports are not being shown MM07
Hi, pals! When I try to see the Monthly Reports (any month) in MM07, I get a popup saying: ------------------------------------------------------------- "File Download Do you want to open or save this file? Name: moneygen.htm Type: Firefox Document, 1,03KB From: C:\Users\yadayada [open] [Save] [Cancel] -------------------------------------------------------------- If I click OPEN, it opens the report in Firefox (!), while the Money windows says: "Navigation to the webpage was canceled. What you can try: - Retype the address" Anyone has seen it before? How to make Money o...

#Name? prints on a report when date shows up on preview
Access 2003 SP 2 Windows XP SP 2 I have created a report (rptFuelmanByForeman) that is based on a Query (qryFuelmanByForeman). I have created a form to select (or enter) a date range that then filters the report based on that date range and populates transactions between those dates. I have created two text boxes on the report that display the start date (txtStartDT) and end date (txtEndDT) entered in the date select form. The control source on the date boxes on the report is "=Forms.frmFuelmanByForeman.txtEndDate" & "...txtStartDate". In Preview mode, the dates d...

Advance Payroll
We use the Overtime Calculations in Advenced Payroll. The problem is when we set up a new employee and assign them to a class, the OT paycode gets set up automatically, but the calculation method doesn't. So the employee is set up with their overtime rate the same as the regular rate. Is there a way to have the overtime calculation method set up automatically or are we stuck with having to go to the OT paycode and adding the calculation method? Thanks for any feedback. ...

Saving Reports
Hi, Is there a simple way to save advanced reports for use on a monthly or weekly basis. I can produce a dynamic report but can't find a way to save this as a template so it can be run on a regular basis. Having to select all the criterea each time you want to run the report is a little annoying, I must be missing something here...? Thanks Nik Once you select all the required criteria for your search, you could click on the 'Save As' button to save the search results with some specific names. For other users to view this search result, go to the 'Saved Views' tab, ...

Creating a QM form that calculates scores?
Is it possible to create a form that has yes/no option controls that calculate scores for whichever one the person chooses and then adds each score for a total score. ...

In a report, is there any way a record will change color if it's different from the previous record
Hi, I am working on a project where I need to color code the report so it will distinguish if the previous record is different from the current record. Because the record will have many similar record, so I am trying to group the record so it's easier to look. In a report, is there any way a record will change color if it's different from the previous record. Thanks for your help. richiecheng@gmail.com wrote: >Hi, I am working on a project where I need to color code the report so >it will distinguish if the previous record is different from the >current record. Because...

Launching Reports from the Submit Order Button in 4.0
My client has several retail locations and they are using CRM in conjunction with their POS system. Therefore, CRM Orders has received a lot of customization to make it as slim, trim, and "Mouse-Click-Free" as possible. In the spirit of reducing even more mouse clicks, they have asked if we could launch a custom report (Basically the Order Report, renamed "Order Receipt") when the "Submit Order" button (re-named by Scribe's CRM to GP Integration) is clicked. The Receipt would render and then be printed for the cusomter. The report is context sensitive...

To view, specifically Units in one report
I have report with a Reporting Tree (9 Unit), when the report is generated, I select only two unit, but I need that the drill down show one report with these unit only If you want multiple units in a single report you will need to use the column layout with unit restrictions on multiple columns Leslie "Pedro" wrote: > I have report with a Reporting Tree (9 Unit), when the report is generated, I > select only two unit, but I need that the drill down show one report with > these unit only Leslie; Each Column layout have the unit restrictions Thanks :-) "Lesl...

Reporting Error ==> very urgent
Hi All, We have installed CRM 4.0 for our organization. We ahd our CRM and SQL on different servers and decided to use Mcirosoft Outlook client. The problem is when we try to generate reports we are unable to get any reports. We always getting an error stating "REPORTING ERROR REPORT CANT BE DISPLAYED" it also stats that to contact Microsoft for details. We are struck with this error for 3 days and the issue is bulging in alarming propositions. Kindly advice me to resolve and overcome this issue. Thanks a lot in advance. elays - ...

Calculate height of listbox?
Is there a forumla to calculate the needed height of a listbox in order to contain a given number of lines of a given point size? Not without a bunch of GDI API work. The AutoSizeTextBox solution on my site shows you how. A far simpler solution, that is almost as accurate would be to use code/logic like: Assumes ListBox control named lbHeight Assumes TestBox control named Text11 Assumes CommandButton named CmdSize Enter a desired number of rows value in Text11 and then click on the CommandButton. Private Sub cmdSize_Click() On Error GoTo Err_cmdSize_Click Dim x As Integer ' conv...

small business reports ...
Dear all Hope this is the right group. Can Mone 2002 create a Trial Balance report? Is there a list of reports availabe in 2006 (Small Business edition) that is accessible somewhere, as I have not been able to locate any such list, and is a Trial Balance one of them. While I am happy using Money and have been for some time, when it comes to reviewing my files the person doing so is a certified accountant and Quick Books user and would like me to convert, I have continued to resist however he continues to press for these accounting reports. Can Money 2002 and or 2006 Small Business ...

Report writer
Hello, I am trying to add the transaction distribution details onto a copy of the PM Blank Document in Report Writer in GP 9. Would you pls advise as I can add the fields containing the data but the data is not appearing on the report. Thanks your comments are appreciated. -- chj@jup Once you were done modifying the report, did you give access to the mdoified report under Security? -- Lyle U chj wrote: > Hello, > > I am trying to add the transaction distribution details onto a copy > of the PM Blank Document in Report Writer in GP 9. Would you pls > advise as I c...

Loan Accounts in Reports
Bugs are in reports, the loan that I pay to my car is divided in 2, principal and interest, but in reports I can't show the two at same time, the reports show only the interest that I'm paying. The budget have the same problem with loans, it shows values that don't exists, I start a new budget, put all the values correctly and finalize it, when I go to budget home there are values that I not put there. There is some fix to those bugs? ...

dividing a text based on a delimiter(dynamic)
i want to create a formula that breaks a list of data (in my case emails) into sets of ten or twenty. each email is delimited with commas and all the emails are contained in one cell that references a column full of singular emails in singular cells. i am so totally lost. any ideas? Hi! Help me. What exactly does "all the emails are contained in one cell that references a column ful of singular emails in singular cells" mean? Al -- Message posted from http://www.ExcelForum.com Here is what happens, I have a cell with "email1@blah.org,email2@bleh.com,email3@bluh.ne...

Monthly reports in Money 2004 Standard
Is there a way to customize the categories used to compose the Monthly Reports? I would like to omit my business travel and living expenses from these reports, as they are fully reimbursed, and have nothing to do with my household finances. Can this be done in 2004? If not, has this feature been rolled into newer versions? Monthly reports are not customizable in any version of Money that is in the wild. "bob2246" <bob2246@discussions.microsoft.com> wrote in message news:3493CA51-596F-4D59-9540-F2D79EA5A618@microsoft.com... > Is there a way to customize the categor...

if zero
hi, In my work sheet i have uncheck tool>option>view>zero values, now in one cell i need the zero value should show either as zero o NIL. If i am giving IF formula it works when it is zero, but when th real balance comes the cell shows as blank. the formula is as follows =IF(H9-H10=0,"nil","") i think after that 'nil' some correction is required, thanks by nowfa -- Message posted from http://www.ExcelForum.com =IF(H9-H10=0,"nil",H9-H10) -- Michael J. Malinsky Pittsburgh, PA "I am a bear of very little brain, and long words bot...

Reprinting/Reporting Past Z Reports
Hi all ~ Just wondering how to pull up a past Z report? Which database table(s) are used? Is there a sql for this so that I can pull up a week or months Z reports in a sql query? Thanks Jerry Jerry, In POS, F4, Batch button, find and double click on the batch you want, page down to the z-out, print. In Manager, Journal, View, find and double click on the batch you want, page down to the zout, print. -- * "Jerry Penna" <jerrypenna@msn.com> wrote in message news:3ac97809.0410011320.1b034dd9@posting.google.com... Hi all ~ Just wondering how to pull up a past Z rep...

Reporting Services has no reports
I cannot see any reports in CRM. We're running SBS 2003 Premium R2 with CRM 3.0 running on this server. The SQL databases and reporting services are installed on a separate 2005 SQL cluster. I've checked that reporting services is running on the cluster. http://<sqlcluster>/reports brings up the SQL Reporting Services and a folder entitled "CompanyName MSCRM" which is empty. How do I determine the physical location of this folder? I've checked in deployment manager on the SBS and CRM Reporting Services is pointing to "http://sqlcluster/reportserver". Is...

Displaying Filter Criteria In Report Header?
I have one report layout "rptStandard" that when it is opened I would like to display the filter criteria in the header. Is there a way to do this? You can put this into the ControlSource of a text box: =[Report].[Filter] Trouble is, sometimes the Filter property contains an artifact from a previous filter, even though the filter is not applied. On a form you could sort this out by testing the FilterOn property, but Access doesn't maintain this property reliably for reports. A better solution is to pass the filter to the report in its OpenArgs (or use a public string...