Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update query (Access 2000)

    I have an update query updating the table products from the table products1, especially the fields branch1 and items1. However I need to improve it. In case the fields in the table products are not null, my function must add the figues in the two fields.
    For example if the products1.branch1 = 7 and if products1.branch1 = 3, the updated field of products.branch1 should be 10 and not 7

    My update function is the following:
    UPDATE products INNER JOIN products1 ON [products].[Productid]=[products1].[Productid] SET products.branch1 = [products1].[branch1], products.items1 = [products1].[items1];

    How can I add in the update query ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: update query (Access 2000)

    Try

    ... SET Products.Branch1 = Nz(Products.Branch1,0)+Nz(Products1.Branch1,0), ...

    with a similar expression for Items1.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update query (Access 2000)

    Thank you so much for your reply I get the error syntax error, missing operatior in the following :

    StrSQL = " UPDATE products INNER JOIN products1 ON products.Productid = products1.Productid SET" & _
    "Products.Branch1 = Nz(Products.Branch1,0)+Nz(Products1.Branch1,0), Products.Items1 = Nz(Products.Items1,0)+Nz(Products1.Items1,0)"
    CurrentDb.Execute StrSQL

    Maybe the link between the two fields ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: update query (Access 2000)

    Insert a space after the word SET (but before the quote "). Does that help?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update query (Access 2000)

    Yes, you are the tops !

Posting Permissions

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