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

    Syntax error in join operation (Access 2000)

    Can you help me! I get the above error in trying to make the following function :

    Public Function UpdateTableProducts1()
    Dim sql As String
    Dim i As Integer
    For i = 1 To 10
    sql = "UPDATE products INNER JOIN products1 ON [products].[Productid]=[products" & i & "].[Productid]" & _
    " SET products1.branch" & i & " = [products].[branch" & i & "], products1.items" & i & " = [products].[items" & i & "];"
    CurrentDb.Execute sql
    Next i
    End Function

    i wanted to rationalize my function, otherwise i had to write 10 times update query :
    1)
    Dim sql As String
    sql = "UPDATE products INNER JOIN products1 ON [products].[Productid]=[products1].[Productid]" & _
    " SET products1.branch1 = [products].[branch1], products1.items1 = [products].[items1];"
    CurrentDb.Execute sql

    2)
    Dim sql As String
    sql = "UPDATE products INNER JOIN products1 ON [products].[Productid]=[products1].[Productid]" & _
    " SET products1.branch2 = [products].[branch2], products1.items2 = [products].[items2];"
    CurrentDb.Execute sql

    etc

    I got an wonderful function depending on the Option Box but this time i need to carry it out without such a box

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

    Re: Syntax error in join operation (Access 2000)

    As far as I can tell, you're always joining products and products1, so there doesn't seem to be any reason to concatenate with i in the JOIN part

    ON [products].[Productid]=[products" & i & "].[Productid]"

    Instead of this, use

    ON [products].[Productid]=[products1].[Productid]"

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

    Re: Syntax error in join operation (Access 2000)

    Thank you for the rely.I get the error now Syntax error missing operator. Did i do it right ?

    sql = "UPDATE products INNER JOIN products1 ON [products].[Productid]=[products1].[Productid]" & i & "].[Productid]" & _
    " SET products1.branch" & i & " = [products].[branch" & i & "], products1.items" & i & " = [products].[items" & i & "];"

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

    Re: Syntax error in join operation (Access 2000)

    Please read my previous reply again carefully.

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

    Re: Syntax error in join operation (Access 2000)

    Just to let you know that following your advices i have sold the problem. Thank you !

Posting Permissions

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