Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: Error (missing operator) in UPDATE

    I have some problems with a query in VB in an Accessdatabase...

    I'm supposed to update one column in a table with a value from another table...

    I get:

    Run-time Error '3075':

    Syntax error (missing operator) in query expression 'b.[Freight Code]
    FROM [7-WW quarterly price step8-1] a'.

    ---

    My code:

    DoCmd.RunSQL "Update [7-WW quarterly price step8-1] SET " & _
    "[Freight Code] = b.[Freight Code] " & _
    "FROM [7-WW quarterly price step8-1] a, [2-Selling_Price_Step1] b " & _
    "WHERE " & _
    "(a.[E/// Component Product Number] = b.[CPN]) AND " & _
    "(a.[WWMS_REGION] = b.[WWMS_REGION])"

    I've looked at similar queries that I have in stored procedures in SQL Server , but I can't figure out what's wrong.

  2. #2
    Join Date
    Nov 2003
    Location
    New Zealand
    Posts
    47
    Hi i have had a similar problem to this as well, i have found that the code for a query in one place will work but needs to be changed for another.

    I found that the problem are the "" and '' or + &. Here are a few example of variations that used that seem to work. Not 100% sure why I need all the """"".

    good luck
    dim machine as string
    machine = rstMachine.field("machineID").value
    sqlMachine = (Nz("Select * from tlbQuoteMachine Where tlbQuoteMachine.Model_ID = """ + machine + """"))


    Dim strMast, strMachine As String
    strMast = CStr(Nz(rstMast.Fields("Mast_ID").Value, ""))
    strMachine = CStr(Nz(rstDetailLine.Fields("MachineID").Value, ""))
    sqlMastSpecs = CStr(Nz("SELECT * from Mast_Machine WHERE Mast_Machine.Mast_ID =""" + strMast + """AND Mast_Machine.Machine_ID =""" + strMachine + """"))

    hope this helps.
    Nerdy Girl

  3. #3
    Join Date
    Jul 2003
    Posts
    123
    All the " s are neccesary if you want to get quotes around some variabels used in the query.

    But I don't have any variabels that needs to put into the query, it's a pure SQL-statement. I even tried it on one row without the & _ didn't work.

    Anymore suggestions someone?

  4. #4
    Join Date
    Jul 2003
    Posts
    123
    ttt

  5. #5
    Join Date
    Jul 2003
    Posts
    123
    I've solved the problem... I get the data I want from another table. I think I got the error because the answer from the WHERE generated more than one row in its answer..

Posting Permissions

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