multiplying a null value

[JohnLute]

I have an unbound field [NetWt] that multiplies 3 other fields:
[UnitCount]*[SubUnitCount]*[DecWtg]

The problem is that [SubUnitCount] is sometimes NULL therefore, the return
is NULL.

How can I code [NetWt] to multiply even when [SubUnitCount] is NULL?

Thanks!!!

 

[Bruce Rusk]

I have an unbound field [NetWt] that multiplies 3 other fields:
[UnitCount]*[SubUnitCount]*[DecWtg]

The problem is that [SubUnitCount] is sometimes NULL therefore, the return
is NULL.

How can I code [NetWt] to multiply even when [SubUnitCount] is NULL?

Thanks!!!

What value do you want subunitcount to be when it's Null? I suppose the
most logical values are 1 (no effect) and 0 (if there are no subunits,
total value is 0).

You can achieve this with
[UnitCount]*Nz([SubUnitCount],X)*[DecWtg]

Where X=0 or 1, depending on which you want.

 

[JohnLute]

Hi, Bruce.

What value do you want subunitcount to be when it's Null?

That's a tricky one because there are times when it HAS to be NULL. Are you
saying that is HAS to have a value in order to properly multiply?

I suppose the
most logical values are 1 (no effect) and 0 (if there are no subunits,
total value is 0).

You can achieve this with
[UnitCount]*Nz([SubUnitCount],X)*[DecWtg]

Where X=0 or 1, depending on which you want.

I experimented with this:
[UnitCount]*Nz([SubUnitCount],0)*[DecWtg]

and entered "0" in [SubUnitCount] but the return was NULL. Ultimately, this
isn't going to work for me but I wanted to try it out.

I guess there's no getting around a NULL value in [SubUnitCount]...? I
suppose I could change all of the NULLS to "0" for when they're null but what
is the reason why the return is NULL?

 

[Bruce Rusk]

Hi, Bruce.


That's a tricky one because there are times when it HAS to be NULL. Are you
saying that is HAS to have a value in order to properly multiply?

Yes ... you can't multiply by something that isn't a number. That's why
I suggested substituting 1 as the value, since multiplying by 1 won't
affect the outcome.

I suppose the
most logical values are 1 (no effect) and 0 (if there are no subunits,
total value is 0).

You can achieve this with
[UnitCount]*Nz([SubUnitCount],X)*[DecWtg]

Where X=0 or 1, depending on which you want.

I experimented with this:
[UnitCount]*Nz([SubUnitCount],0)*[DecWtg]

and entered "0" in [SubUnitCount] but the return was NULL. Ultimately, this
isn't going to work for me but I wanted to try it out.

That's strange; it sounds like there's a NULL somewhere else involved.
It should return zero if SubUnitCount is NULL and the other two fields
have values. If you want a zero value for the whole thing when any part
of it is NULL, you could just use

Nz([UnitCount]*[SubUnitCount]*[DecWtg],0)

I guess there's no getting around a NULL value in [SubUnitCount]...? I
suppose I could change all of the NULLS to "0" for when they're null but what
is the reason why the return is NULL?

That depends on your data, and how your tables are designed. What do
NULL values "mean" in your database -- does a NULL mean that the value
is unknown or missing, or does it mean there are no subunits? Depending
on what a NULL means, you would treat it differently. That's not clear
so far from your question.

 

[JohnLute]

Whooops! I found my error and corrected.

I've also added in order to convert to lbs.:
=[UnitCount]*Nz([SubUnitCount],0)*[DecWtg]*0.002205

This returns 0.00 when [SubUnitCount] is 0. It returns correctly when
[SubUnitCount] is 1 or more.

I expected it to return properly when [SubUnitCount] is 0. Do you see any
reason why it's not?

Thanks!

 

[JohnLute]

Thanks again, Bruce!
--
www.Marzetti.com

Hi, Bruce.


That's a tricky one because there are times when it HAS to be NULL. Are you
saying that is HAS to have a value in order to properly multiply?

Yes ... you can't multiply by something that isn't a number. That's why
I suggested substituting 1 as the value, since multiplying by 1 won't
affect the outcome.

I suppose the
most logical values are 1 (no effect) and 0 (if there are no subunits,
total value is 0).

You can achieve this with
[UnitCount]*Nz([SubUnitCount],X)*[DecWtg]

Where X=0 or 1, depending on which you want.

I experimented with this:
[UnitCount]*Nz([SubUnitCount],0)*[DecWtg]

and entered "0" in [SubUnitCount] but the return was NULL. Ultimately, this
isn't going to work for me but I wanted to try it out.

That's strange; it sounds like there's a NULL somewhere else involved.
It should return zero if SubUnitCount is NULL and the other two fields
have values. If you want a zero value for the whole thing when any part
of it is NULL, you could just use

Nz([UnitCount]*[SubUnitCount]*[DecWtg],0)

I guess there's no getting around a NULL value in [SubUnitCount]...? I
suppose I could change all of the NULLS to "0" for when they're null but what
is the reason why the return is NULL?

That depends on your data, and how your tables are designed. What do
NULL values "mean" in your database -- does a NULL mean that the value
is unknown or missing, or does it mean there are no subunits? Depending
on what a NULL means, you would treat it differently. That's not clear
so far from your question.

 

[Bruce Rusk]

Whooops! I found my error and corrected.

I've also added in order to convert to lbs.:
=[UnitCount]*Nz([SubUnitCount],0)*[DecWtg]*0.002205

This returns 0.00 when [SubUnitCount] is 0. It returns correctly when
[SubUnitCount] is 1 or more.

I expected it to return properly when [SubUnitCount] is 0. Do you see any
reason why it's not?

If you multiply anything by zero, it will be zero. What is "returning
properly"? It sounds like what you want is

[UnitCount]*Nz([SubUnitCount],1)*[DecWtg]*0.002205

because multiplying by one won't change the value. But if there are
records where SubUnitCount=0 you should either change them to NULL (if
that is consistent with the logic of your application) or write a more
complicated return function that checks for the 0 value.

 

[John W. Vinson]

That's a tricky one because there are times when it HAS to be NULL. Are you
saying that is HAS to have a value in order to properly multiply?

Certainly.

Null means "This field is undefined, it does not have any value".

What is the product of 3 and "I don't know what this value might be,
it could be anything"?

John W. Vinson [MVP]

 

[JohnLute]

Well, I'm always surprised/amazed at what Access can do so I thought there
might be a way to make this happen, too!

 

[John W. Vinson]

Well, I'm always surprised/amazed at what Access can do so I thought there
might be a way to make this happen, too!

Well, there is - the NZ function.

I guess what I'm wondering is - "what would you EXPECT to happen?" and
"how would you expect Access to know what you expect to happen?"

John W. Vinson [MVP]