Jennifer:
It might be worth putting a little more flesh on the bones of what we've
said already so that you have a better understanding of why Nulls behave in
the way they do and how to handle them. The important thing to realize is
that Null is not a value, but the absence of a value. The Value property of
a field, control etc can be Null, but Null is not a value as such. About the
nearest we can get to what it means is 'unknown', so it could be anything.
When dealing with numbers for instance it might be zero, it might be one
million, or any other number; when we encounter a Null we just don't know.
In arithmetic operations Null 'propagates'. If you think of Null as
'unknown' then this makes more sense. 123 + unknown for instance could be
123 + 0, 123 + 42, 123 +1,000,000, the possibilities are infinite, so the
result is also unknown, i.e. Null. The same goes for any other type of
arithmetic operation.
Similarly you cannot compare Null with something, e.g. in a query WHERE x =
NULL, as this is saying does x = unknown. Again because the 'unknown' might
be the same as the value of x, or it might not be the result is again
unknown, i.e. Null. This is even true of NULL = NULL as each 'unknown' might
or might not be the same as the other 'unknown', so again the result is
unknown, Null.
There are a number of practical implications of this and ways around them.
The Nz function returns the value of something if it has a value, or another
value if it is Null. Most often this is used to return a zero so that an
arithmetic operation doesn't result in Null, but it can be used to return any
other value you wish.
The same applies when using the Boolean AND or OR operators; TRUE OR NULL
results not in
When comparing something with Null, as we've seen the usual equality
operator, = , can't be used. In VB, however, there is an IsNull function, so
you can use that, IsNull(x), or Not IsNull(x) to determine if x is Null or
not. IN a query there is a similar provision, in this case WHERE x IS NULL,
or WHERE x IS NOT NULL.
There are some situations in which a Null can cause problems which you might
not anticipate. An interesting one is when using the NOT IN predicate in a
query with a subquery, e.g.
SELECT *
FROM a
WHERE x NOT IN
(SELECT y
FROM b);
now lets say there are rows in table a where the value of x is 123 in one
row and in table b the column y contains 345, NULL, 456, 678
Now in plain English what we are asking here in connection with the row
containing 123 in the x column is does any row in b contain 123 in column y,
and if not return the row from a, i.e. the NOT IN predicate evaluates to
TRUE. At first sight that would look to be the case, but if we analyse it a
little its saying:
WHERE 123 <> 456 AND 123 <> NULL AND 123 <> 679
For this expression to evaluate to TRUE, and the row be returned all three
of the comparative operations would have to evaluate to TRUE, but as we've
seen 123 <> NULL results in NULL, so the expression as a whole evaluates to
NULL and the row isn't returned even though there is no row in b with123 in
the y column.
The same does not apply to the IN predicate, however as:
SELECT *
FROM a
WHERE x IN
(SELECT y
FROM b);
is, if we now assume that one row in b contains 123 in y, the same as saying:
WHERE 123 = 123 OR 123 = NULL OR 123 = 679
This time we only require any one of the three comparative operations to
evaluate to TRUE, which the first one does, so the NULL has no effect in this
case.
We can get round the problem with the NOT IN predicate by using the NOT
EXISTS predicate instead and correlating the subquery with the outer query:
SELECT *
FROM a
WHERE NOT EXISTS
(SELECT *
FROM b
WHERE b.y = a.x);
In fact the EXISTS or NOT EXISTS predicates will in most cases also give
better performance than the IN or NOT IN predicates.
Finally its worth looking at the question of whether fields should be
allowed to contain Nulls or not (setting the Required property of a field in
table design prohibits Nulls). For the point of view of the theoretical
database relational model the answer is probably that Nulls are not allowed.
This is because the model requires every column position in every row to
contain a legal value of the attribute type which the column represents (its
worded rather differently in relational theory using different terms, but I
don't want to confuse the issue so I'm deliberately being a little sloppy in
my wording here). In practice most people would say that Nulls are
permissible in a table to represent missing or inapplicable values.
Nevertheless, caution is necessary. Say you have a CreditLimit column in a
Customers table, what does a Null CreditLimit mean? That the customer is
allowed zero credit? That the customer is allowed unlimited credit? We
simply don't know from the Null, its entirely a matter of interpretation as
the Null is 'semantically ambiguous'. Most people would probably say its
means zero credit, at least pending a decision on the customer's
creditworthiness, but if so the value should be zero. With currency data in
fact its almost always the case that Nulls should be prohibited and a
DefaultValue property of zero given to the field. In other cases the same
considerations won't arise; if we don't currently know someone's phone number
for instance then leaving the field Null should cause no problems.
If you are still awake after all that I hope its been of some interest and
will help you when dealing with Nulls in the future.
Ken Sheridan
Stafford, England
jpark3205 said:
Thank you guys so much for your help!
Douglas J. Steele said:
Nz replaces Null values with zeroes. It's necessary because you can't add a
Null value to a numeric value: you'll get Null every time.
You should be able to find out about Nz in the Help file. Since it's a VBA
function, make sure you're in the VB Editor when you look for it.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
jpark3205 said:
Thank you so much for your help!!! I am wondering what exactly Nz is
doing
so that I know better how to apply it. Is there anywhere on the website
that
will tell me this information? Thanks!
Jennifer
:
The HasData property of the report object doesn't appear to have any
relevance here as it's whether the control has data, i.e. is not Null,
which
is the issue:
=(Nz([SumOfAmount],0)-Nz([SumOfCurr Dedn],0))-Nz([SumOfAmount Charged
Back],0)
Ken Sheridan
Stafford, England
:
It is still resulting in a blank value.
More specifically....
I am wanting to subtract amount owed-chargeback amount...
right now the formula for amount owed is
Payout Deducted =
Amt. Owed
=(NZ([SumOfAmount],0)-NZ([SumOfCurr Dedn],0))
For some lines, there is a chargeback amount, but there is not
necessarily a
value in this space. The formula above yeilds a value whether there is
information in the space or not. I am wanting the end result to be:
Amount owed-Chargeback amount=adj. amount
I would like there to be a value in the adj. amount aolumn whether
there is
a chargeback amount or not.
Right now my result is
amount owed - chargeback = adj. amount
2 - 1 = 1
2 - =
I would like for the second line to show a 2 in the adj. amount column.
Is
there anyway this can happen?
:
In your IIf statement, change the Null to zero. When your report has
no
data, you are trying to subtract null, which always results in null.
i was wondering if there was a way to subtract all of these formulas
so
that
the resulting answer willl show up even if there is no information
for the
formulas.
My formula right now is:
=(NZ([SumOfAmount],0)-NZ([SumOfCurr
Dedn],0))-IIf(Report.HasData,([SumOfAmount Charged Back]),Null)
Right now if a row does not contain all three parts the formula
just
appears
blank. I would like for there to be an answer even if there is no
information for portions of the formula. Thanks for your help!
