null values

[Bob Brannon]

Hello,

I am using Access 2000, SP3, on XP Home SP1.

Am I correct that Access defines the result of division by zero or
multiplication by zero as a null value?

If so, then I am having a problem with the Null to Zero (Nz) function. I
have an expression in a query that can result in division by zero. I have
set that expression using Nz this way: Nz(<expression>,0). I have also done
it the other way: Nz(<expression>). In both cases the result is still
showing up as an error, rather than 0.

Can anyone tell me how Nz works or if I might have a problem?

 

[Bruce M. Thompson]

Am I correct that Access defines the result of division by zero or

multiplication by zero as a null value?

No, a "division by zero" runtime error has *no* result - you *cannot* divide any
number by zero, including itself (one *might* think that would result in a value
of one, but ...). Multiplication by zero results in zero.

If so, then I am having a problem with the Null to Zero (Nz) function. I
have an expression in a query that can result in division by zero. I have
set that expression using Nz this way: Nz(<expression>,0). I have also done
it the other way: Nz(<expression>). In both cases the result is still
showing up as an error, rather than 0.

You will need to prevent the "division by error" attempt. That is usually done
using an "IIF()" expression to substitute an alternate value in the event the
divisor "source" returns a value of zero.

Can anyone tell me how Nz works or if I might have a problem?

The Nz() function is explained in Access Help, as is the IIF() function.

 

[Bob Brannon]

Hello Bruce,

Thanks for the reply. I knew that division by zero is undefined, that
is why I asked if "Access defines undefined values as null values", null
meaning nothing. But, from what you said, Access does not consider no
result as a null value. Seems it should.

I had also read the help on Nz and IIf, and used both numerous times. Just
wanted to know what Access considers a null value. That is not clear in
help.

Thanks for the clarification.

Given the above, here is a more complicated request. For example here are
some data:

Record # Total Does Fawns Bucks
1 8 4 3 1
2 11 5 2 4
3 7 5 2 0
4 2 0 1 1
5 1 0 1 0

I need to create one single query that sums the total from all records, but
only averages the ratio of fawns to does (fawns/does) for those records
where does > 0. Is there a way to do this? I know the expressions and
functions for the calculations etc., just need to know how to get it in one
query. I know how to do it in two queries, just need it in the same one.


--
Regards,
Bob Brannon

Am I correct that Access defines the result of division by zero or
multiplication by zero as a null value?

No, a "division by zero" runtime error has *no* result - you *cannot* divide
any
number by zero, including itself (one *might* think that would result in a
value
of one, but ...). Multiplication by zero results in zero.

If so, then I am having a problem with the Null to Zero (Nz) function. I
have an expression in a query that can result in division by zero. I have
set that expression using Nz this way: Nz(<expression>,0). I have also done
it the other way: Nz(<expression>). In both cases the result is still
showing up as an error, rather than 0.

You will need to prevent the "division by error" attempt. That is usually
done
using an "IIF()" expression to substitute an alternate value in the event
the
divisor "source" returns a value of zero.

Can anyone tell me how Nz works or if I might have a problem?

The Nz() function is explained in Access Help, as is the IIF() function.

 

[Bruce M. Thompson]

Thanks for the reply. I knew that division by zero is undefined, that

is why I asked if "Access defines undefined values as null values", null
meaning nothing. But, from what you said, Access does not consider no
result as a null value. Seems it should.

I had also read the help on Nz and IIf, and used both numerous times. Just
wanted to know what Access considers a null value. That is not clear in
help.

Null means "Unknown" or "non-existent", such as a field that has had *no* value
inserted - Null is not "undefined". Access cannot "define" undefined values.
Division-by-zero, however, is a runtime error, not an undefined value (nor is it
"no result") and is much like trying to skin a cat with a scrambled egg (it just
can't be done) only Access tells you that it cannot do the division - the cat
skinning attempt will just leave you with a messy cat.

Thanks for the clarification.

Given the above, here is a more complicated request. For example here are
some data:

Record # Total Does Fawns Bucks
1 8 4 3 1
2 11 5 2 4
3 7 5 2 0
4 2 0 1 1
5 1 0 1 0

I need to create one single query that sums the total from all records, but
only averages the ratio of fawns to does (fawns/does) for those records
where does > 0. Is there a way to do this? I know the expressions and
functions for the calculations etc., just need to know how to get it in one
query. I know how to do it in two queries, just need it in the same one.

I don't know that you can do that from within the same query as your other
calculations, but I'm not as magical with queries as some of my cohorts, so I
would recommend posting that request separately in the queries newsgroup.

 

[Bob Brannon]

Thanks much, and will do.

Thanks for the reply. I knew that division by zero is undefined, that
is why I asked if "Access defines undefined values as null values", null
meaning nothing. But, from what you said, Access does not consider no
result as a null value. Seems it should.

I had also read the help on Nz and IIf, and used both numerous times. Just
wanted to know what Access considers a null value. That is not clear in
help.

Null means "Unknown" or "non-existent", such as a field that has had *no*
value
inserted - Null is not "undefined". Access cannot "define" undefined values.
Division-by-zero, however, is a runtime error, not an undefined value (nor
is it
"no result") and is much like trying to skin a cat with a scrambled egg (it
just
can't be done) only Access tells you that it cannot do the division - the
cat
skinning attempt will just leave you with a messy cat.

Thanks for the clarification.

Given the above, here is a more complicated request. For example here are
some data:

Record # Total Does Fawns Bucks
1 8 4 3 1
2 11 5 2 4
3 7 5 2 0
4 2 0 1 1
5 1 0 1 0

I need to create one single query that sums the total from all records, but
only averages the ratio of fawns to does (fawns/does) for those records
where does > 0. Is there a way to do this? I know the expressions and
functions for the calculations etc., just need to know how to get it in one
query. I know how to do it in two queries, just need it in the same one.

I don't know that you can do that from within the same query as your other
calculations, but I'm not as magical with queries as some of my cohorts, so
I
would recommend posting that request separately in the queries newsgroup.