Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    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
    CurrentDb.Execute strSQL

    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 ?

  2. #2
    Plutonium Lounger
    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) " &

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts