how to make my sql work with zero values

Topic

New Reply

How to make my sql work with zero values

I have a valid sql clause as follows

strSQLSupplier = “UPDATE Products SET Stock = Stock + ” & Me.Quantity & ” WHERE ProductID=” & Me.Productid

The above SQL clause works only if the field

Reply | Quote

Replies

I bet the initial value of Stock for a new item is Null and not zero. Any math with Null produces a Null result.

Reply | Quote

Yeas you are right, the initial value is Null. Is there any way on Access to avoid that?
Best regards

Reply | Quote

set the default value of the field to zero

Reply | Quote

Or change your SQL to this:

strSQLSupplier = “UPDATE Products SET Stock = Stock + ” & Nz(Me.Quantity, 0) & ” WHERE ProductID=” & Me.Productid

Reply | Quote

The best way is to set the DefaultValue of this field to 0 in your table. If you can’t, then use the NZ function (Null-to-Zero):
= NZ ([Stock],0) + ……….

Reply | Quote

It sounds like the value of the stock field is not zero, but is null. Null is not zero and null plus any number is null.

You will probably need to do two things. First change the table definition so that the default for the stock field is zero. Second, you will need to update all of the records in your table with null stock to zero.

Reply | Quote