zero value in a table (Access 2000)

Topic

New Reply

Zero value in a table

In the table products i have a field called stock.This field contains the number of the cartons
we have in stock. I have a function with which i increase the quantity in the field stock with the
number of the cartons i add to this stock.
The function works excellent provided the field stock is not 0.My problem is that when i add a new
product,the function does not increase the quantity in the field stock.I have tried to make the Default value of the product as 0 or 1, but of no use.Probably the fault
lies with my function.Is it possible at all to make my function working starting from a zero quantity?
my function is the following :
strSQLAral = “UPDATE Products SET Stock = Stock + ” & Me.cartons & ” WHERE ProductID=” &
Me.Productid

I will be grateful for any advice

Reply | Quote

Replies

When you add a new product, you won’t be able to update the value until there’s a record to update, so you may be running into a timing issue that can be solved by saving the new record before you try to update it. How are you adding the new product, by query, with a recordset, or what?

Reply | Quote

Something else to consider. Are you sure the value that doesn’t work is really Null and not zero?

Reply | Quote

Dear Sir,
I am afraid you are right.I am mixing up the meaning of Null and Zero.So i am trying to add from a zero or Null value and i cannot do it.In simple words, if i have no product in the warehouse, and add a new quantity, how shall i proceed with my function,and shall i put 0 in the default value of the field?
What i want is to add a new quantity from zero in my stocks of products.
Once again thank you very much .I hope i may solve my problem quite soon

Best regards

Reply | Quote

Modify your sql string, using the NZ( ) function:

……… [stock] = NZ([stock],0) + …….

Reply | Quote