Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    zero value in a table (Access 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: zero value in a table (Access 2000)

    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?
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: zero value in a table (Access 2000)

    Something else to consider. Are you sure the value that doesn't work is really Null and not zero?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: zero value in a table (Access 2000)

    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

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: zero value in a table (Access 2000)

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

    ......... [stock] = NZ([stock],0) + .......
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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