addition with 0

[peljo via AccessMonster.com]

My update function does not always work properly, especially when one of the
fields is 0 or blank.How could i improve my function so that when addding wih
0 not to give 0 ?

My function is the following:
Public Function dummy()
StrSQL = " UPDATE (products1 INNER JOIN products ON products1.Productid =
products.Productid) INNER JOIN [Order Details1] ON " & _
" products1.Productid = [Order Details1].productid SET products1.items1 =
[order details1].[quantity]+[products1].[items1]"
End Function

 

[Allen Browne]

This approach is not going to work.

You can use Nz() to convert the nulls to zeros, e.g.:
SET products1.items1 =
Nz([order details1].[quantity],0) + Nz([products1].[items1],0)

But there are other issues that will prevent this from working reliably,
e.g.:
a) Storing the total in the products table is not a good idea.
It should be calculated when needed.

b) Your query is not aggregating orders by product, so there's lots of room
for error here.

c) Incrementing the total like this guarantees that the total will gradually
become more and more wrong as time goes by.