Results 1 to 2 of 2
2001-12-12, 16:45 #1
- Join Date
- Jan 2001
- Thanked 0 Times in 0 Posts
updating blank fields (Access 2000)
How to update blank fields ?
I have a table named Products with a field called stock.I have a very good function that updates the field stock,increasing it with the number of cartons in table Order details.
The function works well,provided that the field stock has a value higher than 0, or even 0. If the field is blank,than i cannot
The function is the following:
Dim StrSQL as String
StrSql = "UPDATE orders INNER JOIN (products INNER JOIN " & " [order details] ON (products.Productid = [order details].ProductID) "
& "AND (products.Productid = [order details].ProductID)) " & "ON orders.orderid = [order details].OrderID SET " & "products.stock =
products.stock+[order details].cartons " & "WHERE " & strWhere
The same refers to another function:
frm![stock].Value = frm![stock].Value + frm![cartons].Value
again i have to have some value in the field stock in order that the function works.
Is there any way to add 0 with a value and not to receive 0 ?
2001-12-12, 16:54 #2
- Join Date
- Dec 2000
- Sacramento, California, USA
- Thanked 1 Time in 1 Post
Re: updating blank fields (Access 2000)
Is there a reason for nulls in the field? If the field is numeric, you can set the default value to a zero and that will be inserted for all new records created with no value assigned to that field. You would have to go back and run an update query to fill in zeros for any existing records.
On the other hand, if you just want to handle it in your update query, make this change:
& "products.stock = products.stock+Nz([order details].cartons,0) " &Charlotte