#NUM!

  • Thread starter Thread starter bpauley
  • Start date Start date
B

bpauley

This one sounds easy but I can't get it to work
=Sum([ot])/Sum([OTHrs]) is in a txtbox in the Report Footer
It produces #NUM!
I thought this was due to the fields in the syntax being zero.
So I changed the syntax to
=Sum(nz([ot]))/Sum(nz([OTHrs]))
No difference in the result
Please help or tell me where to look for help.

I tried help in access - no dice
Searched google, used to get nice results, not any more.
Looked in Access 2000 at MS Knowledgebase, no good

Thanks
Bill
 
There are several reasons why this might not work, e.g.:

1.You need text boxes named OT and OTHrs on your report, bound to fields
with the same name.

2. One or other of these fields is not a Number field.
For example, if they are an expression in a query, Access may think they are
Text type fields.

3. There are no records in the report.
All calculated expressions error out if there's no data to calculate on.

4. The Sum([OTHrs]) equates to zero.
Division by zero always generates an error.

5. You have the text box in the Page Footer, instead of the Report Footer.
 
Hello Allen,
Thanks for the response.
It is definitely #4 of your reasons below. I should have explained that I
have other text boxes producing the desired results, the OT category is
zero. Can you explain how to handle that situation. I would like the text
box to display a 0, as the others for OT on that line of the report display.
I tried adding nz
=Sum(nz([ot]))/Sum(nz([OTHrs]))
But that did not work either
Thanks again
Bill

Allen Browne said:
There are several reasons why this might not work, e.g.:

1.You need text boxes named OT and OTHrs on your report, bound to fields
with the same name.

2. One or other of these fields is not a Number field.
For example, if they are an expression in a query, Access may think they
are Text type fields.

3. There are no records in the report.
All calculated expressions error out if there's no data to calculate on.

4. The Sum([OTHrs]) equates to zero.
Division by zero always generates an error.

5. You have the text box in the Page Footer, instead of the Report Footer.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

bpauley said:
This one sounds easy but I can't get it to work
=Sum([ot])/Sum([OTHrs]) is in a txtbox in the Report Footer
It produces #NUM!
I thought this was due to the fields in the syntax being zero.
So I changed the syntax to =Sum(nz([ot]))/Sum(nz([OTHrs]))
No difference in the result
Please help or tell me where to look for help.

I tried help in access - no dice
Searched google, used to get nice results, not any more.
Looked in Access 2000 at MS Knowledgebase, no good

Thanks
Bill
Click to expand...
Click to expand...
 
Perhaps something like this:
=IIf(Sum([OTHrs])=0, Null, Sum([ot]) / Sum([OTHrs])


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

bpauley said:
Hello Allen,
Thanks for the response.
It is definitely #4 of your reasons below. I should have explained that I
have other text boxes producing the desired results, the OT category is
zero. Can you explain how to handle that situation. I would like the text
box to display a 0, as the others for OT on that line of the report
display. I tried adding nz
=Sum(nz([ot]))/Sum(nz([OTHrs]))
But that did not work either
Thanks again
Bill

Allen Browne said:
There are several reasons why this might not work, e.g.:

1.You need text boxes named OT and OTHrs on your report, bound to fields
with the same name.

2. One or other of these fields is not a Number field.
For example, if they are an expression in a query, Access may think they
are Text type fields.

3. There are no records in the report.
All calculated expressions error out if there's no data to calculate on.

4. The Sum([OTHrs]) equates to zero.
Division by zero always generates an error.

5. You have the text box in the Page Footer, instead of the Report
Footer.

bpauley said:
This one sounds easy but I can't get it to work
=Sum([ot])/Sum([OTHrs]) is in a txtbox in the Report Footer
It produces #NUM!
I thought this was due to the fields in the syntax being zero.
So I changed the syntax to =Sum(nz([ot]))/Sum(nz([OTHrs]))
No difference in the result
Please help or tell me where to look for help.

I tried help in access - no dice
Searched google, used to get nice results, not any more.
Looked in Access 2000 at MS Knowledgebase, no good
Click to expand...
Click to expand...
Click to expand...
 
Nice!
Thanks so much.
added closing ) for the sake of others reading.
Much appreciated.
Bill

Allen Browne said:
Perhaps something like this:
=IIf(Sum([OTHrs])=0, Null, Sum([ot]) / Sum([OTHrs])


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

bpauley said:
Hello Allen,
Thanks for the response.
It is definitely #4 of your reasons below. I should have explained that I
have other text boxes producing the desired results, the OT category is
zero. Can you explain how to handle that situation. I would like the text
box to display a 0, as the others for OT on that line of the report
display. I tried adding nz
=Sum(nz([ot]))/Sum(nz([OTHrs]))
But that did not work either
Thanks again
Bill

Allen Browne said:
There are several reasons why this might not work, e.g.:

1.You need text boxes named OT and OTHrs on your report, bound to fields
with the same name.

2. One or other of these fields is not a Number field.
For example, if they are an expression in a query, Access may think they
are Text type fields.

3. There are no records in the report.
All calculated expressions error out if there's no data to calculate on.

4. The Sum([OTHrs]) equates to zero.
Division by zero always generates an error.

5. You have the text box in the Page Footer, instead of the Report
Footer.

This one sounds easy but I can't get it to work
=Sum([ot])/Sum([OTHrs]) is in a txtbox in the Report Footer
It produces #NUM!
I thought this was due to the fields in the syntax being zero.
So I changed the syntax to =Sum(nz([ot]))/Sum(nz([OTHrs]))
No difference in the result
Please help or tell me where to look for help.

I tried help in access - no dice
Searched google, used to get nice results, not any more.
Looked in Access 2000 at MS Knowledgebase, no good
Click to expand...
Click to expand...
Click to expand...
 
Back
Top