dividing a field's value by 60

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All

i have a report that is populated via a table and it works fine. However,
due to a change in the underlying data i now need to divide the values by 60
in the report
e.g.
i had
control source = total_hours
i now need
control course = [total_hours]/60

this works fine unless the field's value is 0. In this case i get a #ERROR
returned in the report and i can't find the syntax necessary to supress the
error and return just 0.

i'm using ver 2000

Thanks
JulieD
 
Dividing 0 by 60 should not generate an error. Are you sure you don't have
no value in the field? If the field is Null, use something like:
=Nz([Total_Hrs],0)/60
 
Hi Duane

thanks for your response ... i've gone back to the underlying table and it
definitely has a 0 in there, i had also tried your suggestion and embedded it
in an IIF statement, e.g.
=IIF(ISERROR(Nz([Total_Hrs],0)/60),0,Nz([Total_Hrs],0)/60)
etc and doing anything other than leaving it as
Total_Hrs
seems to end up with the #ERROR
any other ideas????

Cheers
JulieD


Duane Hookom said:
Dividing 0 by 60 should not generate an error. Are you sure you don't have
no value in the field? If the field is Null, use something like:
=Nz([Total_Hrs],0)/60

--
Duane Hookom
MS Access MVP


JulieD said:
Hi All

i have a report that is populated via a table and it works fine. However,
due to a change in the underlying data i now need to divide the values by
60
in the report
e.g.
i had
control source = total_hours
i now need
control course = [total_hours]/60

this works fine unless the field's value is 0. In this case i get a
#ERROR
returned in the report and i can't find the syntax necessary to supress
the
error and return just 0.

i'm using ver 2000

Thanks
JulieD
Click to expand...
Click to expand...
 
Is [Total_Hrs] a field in your report's record source? Did you check to make
sure the name of the control is not the name of a field?

--
Duane Hookom
MS Access MVP


JulieD said:
Hi Duane

thanks for your response ... i've gone back to the underlying table and it
definitely has a 0 in there, i had also tried your suggestion and embedded
it
in an IIF statement, e.g.
=IIF(ISERROR(Nz([Total_Hrs],0)/60),0,Nz([Total_Hrs],0)/60)
etc and doing anything other than leaving it as
Total_Hrs
seems to end up with the #ERROR
any other ideas????

Cheers
JulieD


Duane Hookom said:
Dividing 0 by 60 should not generate an error. Are you sure you don't
have
no value in the field? If the field is Null, use something like:
=Nz([Total_Hrs],0)/60

--
Duane Hookom
MS Access MVP


JulieD said:
Hi All

i have a report that is populated via a table and it works fine.
However,
due to a change in the underlying data i now need to divide the values
by
60
in the report
e.g.
i had
control source = total_hours
i now need
control course = [total_hours]/60

this works fine unless the field's value is 0. In this case i get a
#ERROR
returned in the report and i can't find the syntax necessary to supress
the
error and return just 0.

i'm using ver 2000

Thanks
JulieD
Click to expand...
Click to expand...
Click to expand...
 
Is it possible that the error is triggered when there are no records? If so,
the problem is not dividing zero (or null) by 60, but referring to the
non-existent text box in the report/subreport/section/whatever.

This example shows how to test the HasData property of a subreport, and
yield a zero instead of #Error if there are no records:
=IIf([sub1].[Report].[HasData], Nz([sub1].[Report].[Total_Hrs],0)/60, 0)

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

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

JulieD said:
Hi Duane

thanks for your response ... i've gone back to the underlying table and it
definitely has a 0 in there, i had also tried your suggestion and embedded
it
in an IIF statement, e.g.
=IIF(ISERROR(Nz([Total_Hrs],0)/60),0,Nz([Total_Hrs],0)/60)
etc and doing anything other than leaving it as
Total_Hrs
seems to end up with the #ERROR
any other ideas????

Cheers
JulieD


Duane Hookom said:
Dividing 0 by 60 should not generate an error. Are you sure you don't
have
no value in the field? If the field is Null, use something like:
=Nz([Total_Hrs],0)/60

--
Duane Hookom
MS Access MVP


JulieD said:
Hi All

i have a report that is populated via a table and it works fine.
However,
due to a change in the underlying data i now need to divide the values
by
60
in the report
e.g.
i had
control source = total_hours
i now need
control course = [total_hours]/60

this works fine unless the field's value is 0. In this case i get a
#ERROR
returned in the report and i can't find the syntax necessary to supress
the
error and return just 0.

i'm using ver 2000

Thanks
JulieD
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane

thanks this was it - i had controls & fields named the same!!!!

Cheers
JulieD

Duane Hookom said:
Is [Total_Hrs] a field in your report's record source? Did you check to make
sure the name of the control is not the name of a field?

--
Duane Hookom
MS Access MVP


JulieD said:
Hi Duane

thanks for your response ... i've gone back to the underlying table and it
definitely has a 0 in there, i had also tried your suggestion and embedded
it
in an IIF statement, e.g.
=IIF(ISERROR(Nz([Total_Hrs],0)/60),0,Nz([Total_Hrs],0)/60)
etc and doing anything other than leaving it as
Total_Hrs
seems to end up with the #ERROR
any other ideas????

Cheers
JulieD


Duane Hookom said:
Dividing 0 by 60 should not generate an error. Are you sure you don't
have
no value in the field? If the field is Null, use something like:
=Nz([Total_Hrs],0)/60

--
Duane Hookom
MS Access MVP


Hi All

i have a report that is populated via a table and it works fine.
However,
due to a change in the underlying data i now need to divide the values
by
60
in the report
e.g.
i had
control source = total_hours
i now need
control course = [total_hours]/60

this works fine unless the field's value is 0. In this case i get a
#ERROR
returned in the report and i can't find the syntax necessary to supress
the
error and return just 0.

i'm using ver 2000

Thanks
JulieD
Click to expand...
Click to expand...
Click to expand...
 
I would have suggested this first but you had stated "this works fine unless
the field's value is 0." A value of any number for Total_Hrs should have
caused #ERROR. Not just the 0s.

--
Duane Hookom
MS Access MVP
--

JulieD said:
Hi Duane

thanks this was it - i had controls & fields named the same!!!!

Cheers
JulieD

Duane Hookom said:
Is [Total_Hrs] a field in your report's record source? Did you check to
make
sure the name of the control is not the name of a field?

--
Duane Hookom
MS Access MVP


JulieD said:
Hi Duane

thanks for your response ... i've gone back to the underlying table and
it
definitely has a 0 in there, i had also tried your suggestion and
embedded
it
in an IIF statement, e.g.
=IIF(ISERROR(Nz([Total_Hrs],0)/60),0,Nz([Total_Hrs],0)/60)
etc and doing anything other than leaving it as
Total_Hrs
seems to end up with the #ERROR
any other ideas????

Cheers
JulieD


:

Dividing 0 by 60 should not generate an error. Are you sure you don't
have
no value in the field? If the field is Null, use something like:
=Nz([Total_Hrs],0)/60

--
Duane Hookom
MS Access MVP


Hi All

i have a report that is populated via a table and it works fine.
However,
due to a change in the underlying data i now need to divide the
values
by
60
in the report
e.g.
i had
control source = total_hours
i now need
control course = [total_hours]/60

this works fine unless the field's value is 0. In this case i get a
#ERROR
returned in the report and i can't find the syntax necessary to
supress
the
error and return just 0.

i'm using ver 2000

Thanks
JulieD
Click to expand...
Click to expand...
Click to expand...
 
Back
Top