Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update error (Access 2000)

    I want to update the column sic in the table products with the values of sic in the table products1 1 but what I get is the message Joint expression not suppoted.What is wrong?
    Dim SQL As String
    SQL = " UPDATE products INNER JOIN products1 ON products.productid=products.productid SET " & _
    " products.sic = products1.sic"
    CurrentDb.Execute SQL
    End Function

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

    Re: Update error (Access 2000)

    Please read your JOIN expression carefully. You have used the products table twice.

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update error (Access 2000)

    But with this expression i get now " too few parameters":
    SQL = " UPDATE products INNER JOIN products1 ON products.productid=products1.productid SET " & _
    " products.sic = products1.sic"
    regards

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

    Re: Update error (Access 2000)

    That means that one of the field names is not recognized. Are you sure that both Products and Products1 contain a field named sic?

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update error (Access 2000)

    Thank you Hans.really i had overlooked the right name.How did you know that?it is not a question, just an admiration. But a new problem appears with me..It updates indeed, but only the first product, while i want to update all the products ? I did put an * but i got a syntax error.

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

    Re: Update error (Access 2000)

    The SQL statements will update all records in Products that have a matching ProductID in Products1.
    Records in Products with a ProductID that does not occur in Products1 will not be updated.

Posting Permissions

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