Giving Null a value in a VBA stamtement

[Richnep]

Hi all,

I am writing some VBA code in Access03 in a fields after update event.
The field types are decimal (18,1).


I am trying to write a SQL statement which looks like this:


Docmd.RUNSQL UPDATE tblStorageShip SET tblStorageShip.AmountLeftmg =
([tblStorageShip]![NumberLeftAliquotSize1]*[tblStorageShip]!
[SizeUGAliquotSize1])+([tblStorageShip]!
[NumberLeftAliquotSize2]*[tblStorageShip]![SizeUGAliquotSize2]);


And if one of the fields is a NULL i get a NULL return for that
record. Can I somehow set NULL to euqal 0 for this set of code
instruction so anytime it wencounters a null while executing the
calaculation stament it will see a null and assume it is a ZERO???

Thanks

 

[Guest]

You can use the Nz function to convert any nulls to 0. You have to enclose
each element, not the calculation of the elements.

Docmd.RUNSQL UPDATE tblStorageShip SET tblStorageShip.AmountLeftmg =
(Nz([tblStorageShip]![NumberLeftAliquotSize1],0)*Nz([tblStorageShip]!
[SizeUGAliquotSize1],0))+(Nz([tblStorageShip]!
[NumberLeftAliquotSize2],0)*Nz([tblStorageShip]![SizeUGAliquotSize2],0));

 

[Steve Schapel]

Richnep,

It's a long time since I used the RunSQL method, but... shouldn't the
SQL statement be enclosed in ""s?

Anyway, to your question... use the Nz() function. Probably this will
be the best syntax:
"UPDATE tblStorageShip SET AmountLeftmg =
Nz([NumberLeftAliquotSize1]*[SizeUGAliquotSize1]),0) +
Nz([NumberLeftAliquotSize2]*[SizeUGAliquotSize2],0)"

By the way, as an aside, your need to doing this in the first place
raises some doubts that there is a database design flaw. It is only in
very rare and specific circumstances where it would be generally
regarded as valid to update the value of one field in a table based on
the value of other field(s) in the database. Please post back if you
are interested in some more help with this aspect.

 

[Guest]

Steve,
Your use of the Nz function is incorrect. For example
Nz([NumberLeftAliquotSize1]*[SizeUGAliquotSize1]),0)
If either of the two fields contains a Null, 0 will be returned. You are
converting the results of the multiplcation, not the fields. It should be:
(Nz([NumberLeftAliquotSize1],0)*Nz([SizeUGAliquotSize1],0))
--
Dave Hargis, Microsoft Access MVP

Richnep,

It's a long time since I used the RunSQL method, but... shouldn't the
SQL statement be enclosed in ""s?

Anyway, to your question... use the Nz() function. Probably this will
be the best syntax:
"UPDATE tblStorageShip SET AmountLeftmg =
Nz([NumberLeftAliquotSize1]*[SizeUGAliquotSize1]),0) +
Nz([NumberLeftAliquotSize2]*[SizeUGAliquotSize2],0)"

By the way, as an aside, your need to doing this in the first place
raises some doubts that there is a database design flaw. It is only in
very rare and specific circumstances where it would be generally
regarded as valid to update the value of one field in a table based on
the value of other field(s) in the database. Please post back if you
are interested in some more help with this aspect.

--
Steve Schapel, Microsoft Access MVP

Hi all,

I am writing some VBA code in Access03 in a fields after update event.
The field types are decimal (18,1).


I am trying to write a SQL statement which looks like this:


Docmd.RUNSQL UPDATE tblStorageShip SET tblStorageShip.AmountLeftmg =
([tblStorageShip]![NumberLeftAliquotSize1]*[tblStorageShip]!
[SizeUGAliquotSize1])+([tblStorageShip]!
[NumberLeftAliquotSize2]*[tblStorageShip]![SizeUGAliquotSize2]);


And if one of the fields is a NULL i get a NULL return for that
record. Can I somehow set NULL to euqal 0 for this set of code
instruction so anytime it wencounters a null while executing the
calaculation stament it will see a null and assume it is a ZERO???

Thanks

 

[Steve Schapel]

Hi Dave,

Thanks. However, your analysis is incorrect. ;-)

Multiplying any number by 0 yields 0. Therefore, applying the Nz() to
each of the fields separately, as you have suggested, will still return
0 for the overall product if one of the fields is Null.

 

[Guest]

My analysis is correct.
In this case, because it is multiplication, the result would be 0 using the
Nz functions as I wrote them. 0 * any number is 0, but Null * any number is
Null.

So for multiplication it will work because the Null produced by the
multiplication is converted to 0.

If you were using addition, for example the result would always be 0 if
either element were null, which would return incorrect results.

Although it may work in some circumstances, good coding habits will save a
lot of grief.

--
Dave Hargis, Microsoft Access MVP

Hi Dave,

Thanks. However, your analysis is incorrect. ;-)

Multiplying any number by 0 yields 0. Therefore, applying the Nz() to
each of the fields separately, as you have suggested, will still return
0 for the overall product if one of the fields is Null.

--
Steve Schapel, Microsoft Access MVP

Steve,
Your use of the Nz function is incorrect. For example
Nz([NumberLeftAliquotSize1]*[SizeUGAliquotSize1]),0)
If either of the two fields contains a Null, 0 will be returned. You are
converting the results of the multiplcation, not the fields. It should be:
(Nz([NumberLeftAliquotSize1],0)*Nz([SizeUGAliquotSize1],0))

 

[Richnep]

You can use the Nz function to convert any nulls to 0. You have to enclose
each element, not the calculation of the elements.

Docmd.RUNSQL UPDATE tblStorageShip SET tblStorageShip.AmountLeftmg =
(Nz([tblStorageShip]![NumberLeftAliquotSize1],0)*Nz([tblStorageShip]!
[SizeUGAliquotSize1],0))+(Nz([tblStorageShip]!
[NumberLeftAliquotSize2],0)*Nz([tblStorageShip]![SizeUGAliquotSize2],0));

--
Dave Hargis, Microsoft Access MVP

Hi all,

I am writing some VBA code in Access03 in a fields after update event.
The field types are decimal (18,1).

I am trying to write a SQL statement which looks like this:

Docmd.RUNSQL UPDATE tblStorageShip SET tblStorageShip.AmountLeftmg =
([tblStorageShip]![NumberLeftAliquotSize1]*[tblStorageShip]!
[SizeUGAliquotSize1])+([tblStorageShip]!
[NumberLeftAliquotSize2]*[tblStorageShip]![SizeUGAliquotSize2]);

And if one of the fields is a NULL i get a NULL return for that
record. Can I somehow set NULL to euqal 0 for this set of code
instruction so anytime it wencounters a null while executing the
calaculation stament it will see a null and assume it is a ZERO???

Thanks- Hide quoted text -

- Show quoted text -

I had just remembered NZ after I made the post and it worked
perfectly. Thanks for the confirmation that I am not crazy

 

[Steve Schapel]

Hi Dave,

The way I did it and the way you did it are equivalent. They will
always produce the same answer as each other. I prefer my approach
because it's simpler. I am happy for you to do it your way, but I don't
think "better or worse" can be applied here.

You will note that in my initial reply to Richnep, I took into account
the very factor that you are highlighting. I.e. I applied the Nz()
funtion separately to each of the two operands for the addition. But I
did not apply the Nz() function separately to the operands in the
multiplication, simply because there is no need to do so.