error# due to null values

[Kay Starnes]

I have an expression on my main report
=[Procedures Performed sub].Report.[rtns 2008 - 2]

and a divide by zero here

=([rtn 1])/([proc1])

I get ERROR# when there is no data, can you help me place the null
information in this query in the right place? And also show a zero if it's
null?

 

[Klatuu]

=IIf(Nz([proc1], 0) = 0 , 0, Nz([rtn 1], 0)/([proc1])

 

[Kay Starnes]

That didn't work it says I'm missing a bracket or bar...

Can you tell me where to place to Nz function on the expression
=[procedures performed sub].report.[rtns 2008 - 2]

 

[Klatuu]

Sorry about that. I put in an extra paren. It should be:

=IIf(Nz([proc1], 0) = 0 , 0, Nz([rtn 1], 0)/[proc1])

As to your other question, it would be:

=Nz([procedures performed sub].report.[rtns 2008 - 2], 0)

This: [rtns 2008 - 2]

is an example of bad naming. It is best not to use any spaces or other
special characters other than an underscore in a name. Names really should
be letters, numbers, and underscores. Additionally, when you first see that
name, you think you are looking at math statment.

Also, in that you have something like rtns 2008 (which is probably returns
for the year 2008) suggests there may be problems with your database design.

--
Dave Hargis, Microsoft Access MVP

That didn't work it says I'm missing a bracket or bar...

Can you tell me where to place to Nz function on the expression
=[procedures performed sub].report.[rtns 2008 - 2]

I have an expression on my main report
=[Procedures Performed sub].Report.[rtns 2008 - 2]

and a divide by zero here

=([rtn 1])/([proc1])

I get ERROR# when there is no data, can you help me place the null
information in this query in the right place? And also show a zero if it's
null?

 

[Kay Starnes]

I don't think you got my response....I fixed the zero's at the sub report
level and then tried your formula again and it worked.

Now I have a NULL problem from the totals that are pulling from the sub
report
Field: > =[Procedures Performed sub].Report.[rtns 2008 - 2]

If there is no data on the sub report for the person then it gives me the
Error# message. The person doesn't have any information and they are not
showing on the sub at all can I get it to show zeros.

 

[Klatuu]

I posted the formula in my previous response. Any time you need to protect
against Null values, use the Nz function. For details, look it up in Access
Help.
--
Dave Hargis, Microsoft Access MVP

I don't think you got my response....I fixed the zero's at the sub report
level and then tried your formula again and it worked.

Now I have a NULL problem from the totals that are pulling from the sub
report
Field: > =[Procedures Performed sub].Report.[rtns 2008 - 2]

If there is no data on the sub report for the person then it gives me the
Error# message. The person doesn't have any information and they are not
showing on the sub at all can I get it to show zeros.

I have an expression on my main report
=[Procedures Performed sub].Report.[rtns 2008 - 2]

and a divide by zero here

=([rtn 1])/([proc1])

I get ERROR# when there is no data, can you help me place the null
information in this query in the right place? And also show a zero if it's
null?

 

[Kay Starnes]

Thank you for your help.

Sorry about that. I put in an extra paren. It should be:

=IIf(Nz([proc1], 0) = 0 , 0, Nz([rtn 1], 0)/[proc1])

As to your other question, it would be:

=Nz([procedures performed sub].report.[rtns 2008 - 2], 0)

This: [rtns 2008 - 2]

is an example of bad naming. It is best not to use any spaces or other
special characters other than an underscore in a name. Names really should
be letters, numbers, and underscores. Additionally, when you first see that
name, you think you are looking at math statment.

Also, in that you have something like rtns 2008 (which is probably returns
for the year 2008) suggests there may be problems with your database design.

--
Dave Hargis, Microsoft Access MVP

That didn't work it says I'm missing a bracket or bar...

Can you tell me where to place to Nz function on the expression
=[procedures performed sub].report.[rtns 2008 - 2]

I have an expression on my main report
=[Procedures Performed sub].Report.[rtns 2008 - 2]

and a divide by zero here

=([rtn 1])/([proc1])

I get ERROR# when there is no data, can you help me place the null
information in this query in the right place? And also show a zero if it's
null?