Runtime Error 3131 : syntax error in FROM clause

[dominofly]

Hi,

Im having some trouble with code i have created for an append query.
I have no idea why, but im getting the error above. To me the query statement looks fine but im sure its something really silly i have missed out.
Please help!!

Dim zCo     As String
Dim zLine   As String
Dim zSQL    As String

    If IsNull(Me!txtCompany) Then
        MsgBox "You must enter the company name you intend to update!!"
    Exit Sub
    End If
    If IsNull(Me!txtNominal) Then
       MsgBox "You need to enter the nominal details before you continue!!"
    Exit Sub
    End If

        zCo = "qap" & Replace(Me![txtCompany], "'", "''") & "SGA1"
        zLine = Replace(Me![txtNominal], "'", "''")
 
        zSQL = "INSERT INTO TEMP_SGA_CM ( [Company], [Our_Reference], [Year], [Period], [Cost_Centre], [Reference], [Transaction_Date], [Description], [Job_Code], [GL_Code], [Department_Code], [Value], [Rate], [Net_Value], [STG], [Department], [GL_Group], [Description_GL], [Description_CC]) " & _
        "SELECT [Company], [idxOurRef1], [thYear], [thPeriod], [tlCostCentre], [thAcCode], [tlTransDate], [tlDescr], [tlJobCode], [tlGLCode], [tlDepartment], [value], [xRate], [NetValue], [Stg], [Desc], [GL_Group], [Description_GL], [Description] " & _
        "FROM " & qCo & _
        " WHERE [idxOurRef1] = '" & zLine & "'"
        CurrentDb.Execute zSQL, DB_FAILONERROR

MsgBox "Line item for Company has been updated"

 

[Darren Bartrup]

Should the qCo variable in your FROM clause be zCO? That's the only fault I can see in it

 

[jasmith4]

Right -- is there a typo in your actual code or just here on the forum?

        zSQL = "INSERT INTO TEMP_SGA_CM ( [Company], [Our_Reference], [Year], [Period], [Cost_Centre], [Reference], [Transaction_Date], [Description], [Job_Code], [GL_Code], [Department_Code], [Value], [Rate], [Net_Value], [STG], [Department], [GL_Group], [Description_GL], [Description_CC]) " & _
        "SELECT [Company], [idxOurRef1], [thYear], [thPeriod], [tlCostCentre], [thAcCode], [tlTransDate], [tlDescr], [tlJobCode], [tlGLCode], [tlDepartment], [value], [xRate], [NetValue], [Stg], [Desc], [GL_Group], [Description_GL], [Description] " & _
        "FROM " & [B][COLOR=red]zCo[/COLOR][/B] & _
        " WHERE [idxOurRef1] = '" & zLine & "'"

If that's not it, set a breakpoint on the CurrentDb().Execute statement (click on it and hit F9), add the statement Debug.Print zSQL right before it, and when you run it, and it stops on that statement, hit Ctrl-G to see what it printed.

 

[dominofly]

Thanks Guys,

I have run the process as explained. I also changed the variable qCo to zCo.

in the Immediate window the variables zCo and zLine both give the expected stings.

However now the error "run-time error 3061: Too few parameters. Expected 2", appears.

 

[revans]

You should be able to cut and paste the debug.print-ed sql into a fresh query, switch to qbe view and see if there are any errors. You can run it from there to see if it returns what you think it should as well. I find this technique helps tremendously when creating sql strings in code.

hth,

Rich

 

[jasmith4]

That means that two of the strings within brackets are wrong: they don't match field names, so they're understood to be parameters to the query.