Null values and calculations

  • Thread starter Thread starter Karina M ;)
  • Start date Start date
K

Karina M ;)

I have a null value that appears as a zero in a report as the control is
"=nZ([PRS TAT],0)". Yet, I am unable to deduct this zero value from a second
value. The field just comes out blank. Is this null zero a true zero or can
is not be used for calculations. If not, any suggestions on what I can use
instead?
Thanks!
 
What is the other calculation? Nz() returns a variant data type so you may
need to wrap it in Val()
=Val(Nz([PRS TAT],0))
 
Hmmm...that made the report return with no values at all. There were #Error
messages where this formaul was being used in a calculations. Are there any
other factors that might cause this?
Thanks!
 
Lots of things can cause #Error. And once Access discovers a calculated
control it cannot resolve, it gives up on the others. Consequently you may
have only one bad one, yet the others show #Error as well.

A common cause is a control that has the same name as a field, but is bound
to something else. For example, if this control is named PRS TAT, but bound
to the expression you posted, Access can't figure out what to do with it.
Change its Name property to (say) txtPrsTat.

Sometimes Access will barf if you refer to a field that is not actually on
the report, e.g. if there is no text box for PRS TAT. It seems that the
optimizer doesn't bother fetching all fields if there's no control for the
field (depending on what other sorting/grouping is going on.) Add a text box
for the field, and hide it by setting its Visible property to No.

Other causes include an erroneous expression, division by zero, or bad data
types (e.g. trying to sum text fields.)

That last one is particularly releveant when you use Nz() in a query. JET
will treat the results as Text instead of Number. You can see that in any
query, as the column output is left-aligned like text, instead of
right-aligned as a number. Therefore I prefer to use IIf() rather than Nz()
in a query. For example, instead of:
Nz([PRS TAT], 0)
use:
IIf([PRS TAT] Is Null, 0, [PRS TAT])

In your case, the expression is in the Control Source of a text box, so you
might be able to circumvent the problem merely by setting the Format
property of the text box to something numeric, e.g. General Number, or
Currency.

So how do you find which of your calculated text boxes is causing the error?
Divide the task in half repeatedly until you pin it down. Without saving,
delete half the calculated controls. Result:
- Still there? delete half the remaining ones.
- Gone? put half them back.

HTH

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

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

Karina M ;) said:
Hmmm...that made the report return with no values at all. There were
#Error
messages where this formaul was being used in a calculations. Are there
any
other factors that might cause this?
Thanks!

Karina M ;) said:
I have a null value that appears as a zero in a report as the control is
"=nZ([PRS TAT],0)". Yet, I am unable to deduct this zero value from a
second
value. The field just comes out blank. Is this null zero a true zero or
can
is not be used for calculations. If not, any suggestions on what I can
use
instead?
Thanks!
Click to expand...
Click to expand...
 
There is another cause of #error. If the report returns no records, then any
value calculated in the report header or footer section will display #error.

I think you need to tell us more significant about your report's record
source etc.

--
Duane Hookom
Microsoft Access MVP


Allen Browne said:
Lots of things can cause #Error. And once Access discovers a calculated
control it cannot resolve, it gives up on the others. Consequently you may
have only one bad one, yet the others show #Error as well.

A common cause is a control that has the same name as a field, but is bound
to something else. For example, if this control is named PRS TAT, but bound
to the expression you posted, Access can't figure out what to do with it.
Change its Name property to (say) txtPrsTat.

Sometimes Access will barf if you refer to a field that is not actually on
the report, e.g. if there is no text box for PRS TAT. It seems that the
optimizer doesn't bother fetching all fields if there's no control for the
field (depending on what other sorting/grouping is going on.) Add a text box
for the field, and hide it by setting its Visible property to No.

Other causes include an erroneous expression, division by zero, or bad data
types (e.g. trying to sum text fields.)

That last one is particularly releveant when you use Nz() in a query. JET
will treat the results as Text instead of Number. You can see that in any
query, as the column output is left-aligned like text, instead of
right-aligned as a number. Therefore I prefer to use IIf() rather than Nz()
in a query. For example, instead of:
Nz([PRS TAT], 0)
use:
IIf([PRS TAT] Is Null, 0, [PRS TAT])

In your case, the expression is in the Control Source of a text box, so you
might be able to circumvent the problem merely by setting the Format
property of the text box to something numeric, e.g. General Number, or
Currency.

So how do you find which of your calculated text boxes is causing the error?
Divide the task in half repeatedly until you pin it down. Without saving,
delete half the calculated controls. Result:
- Still there? delete half the remaining ones.
- Gone? put half them back.

HTH

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

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

Karina M ;) said:
Hmmm...that made the report return with no values at all. There were
#Error
messages where this formaul was being used in a calculations. Are there
any
other factors that might cause this?
Thanks!

Karina M ;) said:
I have a null value that appears as a zero in a report as the control is
"=nZ([PRS TAT],0)". Yet, I am unable to deduct this zero value from a
second
value. The field just comes out blank. Is this null zero a true zero or
can
is not be used for calculations. If not, any suggestions on what I can
use
instead?
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Allen for the extra info.
I've never used IIF statements in queries, just in reports. Where would I
place the IIF statement within the query? Any other fields I need to set to
make it work?

Duane:
My query has some calculated fields from tables and some coming directly
from a table. The ones coming directly may have null values which I need to
be represented by a TRUE zero, as I perform calculations based on these
values. nZ made all null. Allen's answer sounds useful as the values would
come into the report in the favored format. I'll try this first.
Thanks!

Duane Hookom said:
There is another cause of #error. If the report returns no records, then any
value calculated in the report header or footer section will display #error.

I think you need to tell us more significant about your report's record
source etc.

--
Duane Hookom
Microsoft Access MVP


Allen Browne said:
Lots of things can cause #Error. And once Access discovers a calculated
control it cannot resolve, it gives up on the others. Consequently you may
have only one bad one, yet the others show #Error as well.

A common cause is a control that has the same name as a field, but is bound
to something else. For example, if this control is named PRS TAT, but bound
to the expression you posted, Access can't figure out what to do with it.
Change its Name property to (say) txtPrsTat.

Sometimes Access will barf if you refer to a field that is not actually on
the report, e.g. if there is no text box for PRS TAT. It seems that the
optimizer doesn't bother fetching all fields if there's no control for the
field (depending on what other sorting/grouping is going on.) Add a text box
for the field, and hide it by setting its Visible property to No.

Other causes include an erroneous expression, division by zero, or bad data
types (e.g. trying to sum text fields.)

That last one is particularly releveant when you use Nz() in a query. JET
will treat the results as Text instead of Number. You can see that in any
query, as the column output is left-aligned like text, instead of
right-aligned as a number. Therefore I prefer to use IIf() rather than Nz()
in a query. For example, instead of:
Nz([PRS TAT], 0)
use:
IIf([PRS TAT] Is Null, 0, [PRS TAT])

In your case, the expression is in the Control Source of a text box, so you
might be able to circumvent the problem merely by setting the Format
property of the text box to something numeric, e.g. General Number, or
Currency.

So how do you find which of your calculated text boxes is causing the error?
Divide the task in half repeatedly until you pin it down. Without saving,
delete half the calculated controls. Result:
- Still there? delete half the remaining ones.
- Gone? put half them back.

HTH

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

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

Karina M ;) said:
Hmmm...that made the report return with no values at all. There were
#Error
messages where this formaul was being used in a calculations. Are there
any
other factors that might cause this?
Thanks!

:

I have a null value that appears as a zero in a report as the control is
"=nZ([PRS TAT],0)". Yet, I am unable to deduct this zero value from a
second
value. The field just comes out blank. Is this null zero a true zero or
can
is not be used for calculations. If not, any suggestions on what I can
use
instead?
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
I have a null value that appears as a zero in a report as the control is
"=nZ([PRS TAT],0)". Yet, I am unable to deduct this zero value from a second
value. The field just comes out blank. Is this null zero a true zero or can
is not be used for calculations. If not, any suggestions on what I can use
instead?
Thanks!
Click to expand...

only use nz when you get errors related to illegal use of null. Ie you
are trying to pass a null variable to a function

udeing math functions ie sum and avg will ignore null values

a) ie avg(2,null,2,2) = 2

b) avg (2,0,2,2)= 6/4 1.5

try like what Allen said I use iif(test,true,false) a way more the nz
IIf([PRS TAT] Is Null, 0, [PRS TAT])

if you want b
avg( IIf([PRS TAT] Is Null, 0, [PRS TAT]) )
else

avg( column) will ignore the nulls

remember in math/computers null and 0 are totally different things
one means don't know or don't care
one means I looked and there are none.
 
You can use the IIf() expression in the Control Source of a text box.

Duane is right: another cause of #Error is calculated expressions when the
report has no records. Test the report's HasData property in IIf().

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

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

Karina M ;) said:
Thanks, Allen for the extra info.
I've never used IIF statements in queries, just in reports. Where would I
place the IIF statement within the query? Any other fields I need to set
to
make it work?

Duane:
My query has some calculated fields from tables and some coming directly
from a table. The ones coming directly may have null values which I need
to
be represented by a TRUE zero, as I perform calculations based on these
values. nZ made all null. Allen's answer sounds useful as the values
would
come into the report in the favored format. I'll try this first.
Thanks!

Duane Hookom said:
There is another cause of #error. If the report returns no records, then
any
value calculated in the report header or footer section will display
#error.

I think you need to tell us more significant about your report's record
source etc.

--
Duane Hookom
Microsoft Access MVP


Allen Browne said:
Lots of things can cause #Error. And once Access discovers a calculated
control it cannot resolve, it gives up on the others. Consequently you
may
have only one bad one, yet the others show #Error as well.

A common cause is a control that has the same name as a field, but is
bound
to something else. For example, if this control is named PRS TAT, but
bound
to the expression you posted, Access can't figure out what to do with
it.
Change its Name property to (say) txtPrsTat.

Sometimes Access will barf if you refer to a field that is not actually
on
the report, e.g. if there is no text box for PRS TAT. It seems that the
optimizer doesn't bother fetching all fields if there's no control for
the
field (depending on what other sorting/grouping is going on.) Add a
text box
for the field, and hide it by setting its Visible property to No.

Other causes include an erroneous expression, division by zero, or bad
data
types (e.g. trying to sum text fields.)

That last one is particularly releveant when you use Nz() in a query.
JET
will treat the results as Text instead of Number. You can see that in
any
query, as the column output is left-aligned like text, instead of
right-aligned as a number. Therefore I prefer to use IIf() rather than
Nz()
in a query. For example, instead of:
Nz([PRS TAT], 0)
use:
IIf([PRS TAT] Is Null, 0, [PRS TAT])

In your case, the expression is in the Control Source of a text box, so
you
might be able to circumvent the problem merely by setting the Format
property of the text box to something numeric, e.g. General Number, or
Currency.

So how do you find which of your calculated text boxes is causing the
error?
Divide the task in half repeatedly until you pin it down. Without
saving,
delete half the calculated controls. Result:
- Still there? delete half the remaining ones.
- Gone? put half them back.

HTH

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

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

Hmmm...that made the report return with no values at all. There were
#Error
messages where this formaul was being used in a calculations. Are
there
any
other factors that might cause this?
Thanks!

:

I have a null value that appears as a zero in a report as the
control is
"=nZ([PRS TAT],0)". Yet, I am unable to deduct this zero value from
a
second
value. The field just comes out blank. Is this null zero a true zero
or
can
is not be used for calculations. If not, any suggestions on what I
can
use
instead?
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top