SQL VBA Run-Time Error 3075: Syntax error(missing operator) in query expression

Question

I'm trying to follow a set of loop SQL code for my specific need in an Access Database. And I run into this error

Run-Time Error 3075: Syntax error(missing operator) in query expression 'SYRLIB_FLCRDD14.CRARNO=SYRLIB_FPCRDHDR.CHARNO From SYRLIB_FLCRDD14 INNER JOIN SYRLIB_FPCUSMAS on SYRLIB_FLCRDD14.CRCUST=SYRLIB_FPCUSMAS.CNUMBR From SYRLIB_FLCRDD14 INNER JOIN SYRLIB_FPSECFIL on SYRLIB_FLCRDD14.CRCLRK=SYRLIB_FPSECFIL.SECNUM FROM SYRLIB FLCRDD1'.

(for the bolded part, I think it should be SYRLIB_FLCRDD14, I've searched through the entire code, all 23 matches are spelled as "FLCRDD14", I don't see a "FLCRDD1". Am I missing something?)

Thank you for all the comment below so far! I've re-edited and re-pasted the entire set of the code based on the below suggestions. I've also taken out the bracket around SELECT and taken out the comma after [Issue by]. I used the immediate window and ran the new query. It gives a different error: Syntax error(missing operator) in query expression 'SYRLIB_FLCRDD14.CRCMMT'.

Sub RunDistrackData()  'Pulls Customer Rebate Data
'On Error GoTo Err_Handler:

    'DoCmd.SetWarnings False
    'DoCmd.Hourglass True

    Dim rst2 As ADODB.Recordset
    Set rst2 = New ADODB.Recordset
    rst2.Open "[TblDC]", CurrentProject.Connection

    Dim sCHAR As String
    Dim sName As String
    Dim sNUM As String
    Dim SQry As String
    Dim mySQL As String
    Dim qfd As QueryDef
    Dim db As Database
    Set db = CurrentDb
    Dim bTime As Date
    Dim eTime As Date
    'bTime = Now()

    rst2.MoveFirst

    DoCmd.OpenQuery "Clear Data", acViewNormal, acEdit
    Do While Not rst2.EOF

    sCHAR = rst2.Fields("DC CHAR")
    sNUM = rst2.Fields("DCNum")
    sName = rst2.Fields("DCName")


mySQL = "INSERT INTO [Data]([DC],[DC Name],[Customer Type],[Printed Date],[Entered Date],"
mySQL = mySQL & " [Customer Number],[Customer Name],[Transfer Invoice],[Item Number],"
mySQL = mySQL & " [Item Description],[CRTYPE],[Reason for Return],[Dist Return Code Override],"
mySQL = mySQL & " [Qty Returned],[CRRPCS],[CRCRTT],[CRDBCR],[CRINV#],[CRCMMT],"
mySQL = mySQL & " [GEN CMMT1],[GEN CMMT2],[GEN CMMT3],[Issue by])"

mySQL = mySQL & " SELECT " & sNUM & " AS [DC],  '" & sName & "'  AS [DC Name], "

mySQL = mySQL & " " & sCHAR & "LIB_FPCUSMAS.CSBSTY AS [Customer Type], "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRCMDT AS [Printed Date], "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRPRDT AS [Entered Date], "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRCUST AS [Customer Number], "
mySQL = mySQL & " " & sCHAR & "LIB_FPCUSMAS.CLNAME AS [Customer Name], "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRARNO AS [Transfer Invoice], "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRITEM AS [Item Number], "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRDESC AS [Item Description], "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRTYPE, "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRRESN AS [Reason for Return], "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRDSOV AS [Dist Return Code Override], "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRQTSR AS [Qty Returned], "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRRPCS, "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRCRTT, "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRDBCR, "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.[CRINV#], "
mySQL = mySQL & " " & sCHAR & "LIB_FLCRDD14.CRCMMT, "
mySQL = mySQL & " " & sCHAR & "LIB_FPCRDHDR.CHCMT1 AS [GEN CMMT 1], "
mySQL = mySQL & " " & sCHAR & "LIB_FPCRDHDR.CHCMT2 AS [GEN CMMT 2], "
mySQL = mySQL & " " & sCHAR & "LIB_FPCRDHDR.CHCMT3 AS [GEN CMMT 3], "
mySQL = mySQL & " " & sCHAR & "LIB_FPSECFIL.SECNAM AS [Issue by] "






'change below to connect FLCRDD14 table to whichever order table adding.  add order details above

mySQL = mySQL & " From " & sCHAR & "LIB_FLCRDD14 INNER JOIN " & sCHAR & "LIB_FPCRDHDR"
mySQL = mySQL & " ON " & sCHAR & "LIB_FLCRDD14.CRARNO = " & sCHAR & "LIB_FPCRDHDR.CHARNO"
mySQL = mySQL & " From " & sCHAR & "LIB_FLCRDD14 INNER JOIN " & sCHAR & "LIB_FPCUSMAS"
mySQL = mySQL & " ON " & sCHAR & "LIB_ FLCRDD14.CRCUST = " & sCHAR & "LIB_FPCUSMAS.CNUMBR"
mySQL = mySQL & " From " & sCHAR & "LIB_FLCRDD14 INNER JOIN " & sCHAR & "LIB_FPSECFIL "
mySQL = mySQL & " ON " & sCHAR & "LIB_FLCRDD14.CRCLRK = " & sCHAR & "LIB_FPSECFIL.SECNUM"


mySQL = mySQL & " FROM " & sCHAR & "LIB_FLCRDD14"
mySQL = mySQL & " WHERE (((" & sCHAR & "LIB_FPGLJNLDBK.CRITEM)"
mySQL = mySQL & " Like '*358300*') "

mySQL = mySQL & " AND ((" & sCHAR & "LIB_FPGLJNLDBK.CRCMDT)"
mySQL = mySQL & " Between [Forms]![Form1]![StartDate] "
mySQL = mySQL & " And [Forms]![Form1]![EndDate]));"

mySQL = mySQL & " AND ((" & sCHAR & "LIB_FPGLJNLDBK.CRRESN)"
mySQL = mySQL & " Like ('*MS*'Or '*RB*'Or '*GR*'))"

mySQL = mySQL & " AND ((" & sCHAR & "LIB_FPGLJNLDBK.CRITEM)>0))"


Debug.Print mySQL

    SQry = "Income" & sCHAR
    Set qfd = db.CreateQueryDef(SQry, mySQL)
    qfd.ODBCTimeout = 9999
    RefreshDatabaseWindow
    DoCmd.OpenQuery "Data" & sCHAR
    db.QueryDefs.Delete (SQry)

    rst2.MoveNext
    Loop

    'add additional queries here
    'DoCmd.OpenQuery "..............."

    DoCmd.OpenQuery "Add MIF Data"


    'DoCmd.SetWarnings True
    'DoCmd.Hourglass False

    rst2.Close
    Set rst2 = Nothing

    'eTime = Now()

    'MsgBox "Report run is completed.  Run Time = " & Format([eTime] - [bTime], "hh:nn:ss")

    End Sub

Please provide the final value of the mySQL variable. We have no way to ascertain this from the code snippet (which is incomplete, by the way). — Pittsburgh DBA
– Pittsburgh DBA, Commented May 1, 2015 at 16:04
As @PittsburghDBA requested, please show us what Access shows you for Debug.Print mySQL — HansUp
– HansUp, Commented May 1, 2015 at 16:12
SELECT being in brackets is a problem. It would still be nice to inspect the final value of mySQL despite this. — Pittsburgh DBA
– Pittsburgh DBA, Commented May 1, 2015 at 16:12

HansUp · Accepted Answer · 2015-05-01 21:49:55Z

At the end of this answer, I pasted the full text of the first INSERT statement created from your code's main loop.

Since my copy of your db can't connect to your ODBC data sources, I can't test that statement.

However I can see several problems:

SYRLIB_FLCRD in the SELECT fields list is missing a comma before the next field.
I'm unsure what SYRLIB_FLCRD is. It looks like a fragment of the table name, not followed by an actual field name.
Multiple FROM clauses.
a stray semicolon (;) in the middle of the WHERE clause
Like ('*MS*'Or '*RB*'Or '*GR*') is not valid in Access SQL

Those may not be all the errors. But they are enough to convince me you should forget about the VBA code for now. Concentrate only on the query. Design and test with the SELECT piece. After you get that working, adapt it as an INSERT.

Return to the VBA code only after you have an INSERT which executes without error and does what you need it to do.

INSERT INTO [Data]
    (
        [DC],
        [DC Name],
        [Customer Type],
        [Printed Date],
        [Entered Date],
        [Customer Number],
        [Customer Name],
        [Transfer Invoice],
        [Item Number],
        [Item Description],
        [CRTYPE],
        [Reason for Return],
        [Dist Return Code Override],
        [Qty Returned],
        [CRRPCS],
        [CRCRTT],
        [CRDBCR],
        [CRINV#],
        [CRCMMT],
        [GEN CMMT1],
        [GEN CMMT2],
        [GEN CMMT3],
        [Issue by]
    )
SELECT
    3 AS [DC],
    'SYRACUSE'  AS [DC Name],
    SYRLIB_FPCUSMAS.CSBSTY AS [Customer Type],
    SYRLIB_FLCRDD14.CRCMDT AS [Printed Date],
    SYRLIB_FLCRDD14.CRPRDT AS [Entered Date],
    SYRLIB_FLCRDD14.CRCUST AS [Customer Number],
    SYRLIB_FPCUSMAS.CLNAME AS [Customer Name],
    SYRLIB_FLCRDD14.CRARNO AS [Transfer Invoice],
    SYRLIB_FLCRDD14.CRITEM AS [Item Number],
    SYRLIB_FLCRDD14.CRDESC AS [Item Description],
    SYRLIB_FLCRDD14.CRTYPE,
    SYRLIB_FLCRDD14.CRRESN AS [Reason for Return],
    SYRLIB_FLCRDD14.CRDSOV AS [Dist Return Code Override],
    SYRLIB_FLCRDD14.CRQTSR AS [Qty Returned],
    SYRLIB_FLCRDD14.CRRPCS,
    SYRLIB_FLCRDD14.CRCRTT,
    SYRLIB_FLCRDD14.CRDBCR,
    SYRLIB_FLCRDD14.[CRINV#],
    SYRLIB_FLCRD
    D14.CRCMMT,
    SYRLIB_FPCRDHDR.CHCMT1 AS [GEN CMMT 1],
    SYRLIB_FPCRDHDR.CHCMT2 AS [GEN CMMT 2],
    SYRLIB_FPCRDHDR.CHCMT3 AS [GEN CMMT 3],
    SYRLIB_FPSECFIL.SECNAM AS [Issue by]
From SYRLIB_FLCRDD14 INNER JOIN SYRLIB_FPCRDHDR ON SYRLIB_FLCRDD14.CRARNO = SYRLIB_FPCRDHDR.CHARNO
From SYRLIB_FLCRDD14 INNER JOIN SYRLIB_FPCUSMAS ON SYRLIB_ FLCRDD14.CRCUST = SYRLIB_FPCUSMAS.CNUMBR
From SYRLIB_FLCRDD14 INNER JOIN SYRLIB_FPSECFIL  ON SYRLIB_FLCRDD14.CRCLRK = SYRLIB_FPSECFIL.SECNUM
FROM SYRLIB_FLCRDD14
WHERE
    (((SYRLIB_FPGLJNLDBK.CRITEM) Like '*358300*')
    AND ((SYRLIB_FPGLJNLDBK.CRCMDT)
    Between [Forms]![Form1]![StartDate] And [Forms]![Form1]![EndDate])); AND ((SYRLIB_FPGLJNLDBK.CRRESN)
    Like ('*MS*'Or '*RB*'Or '*GR*')) AND ((SYRLIB_FPGLJNLDBK.CRITEM)>0))

Collectives™ on Stack Overflow

SQL VBA Run-Time Error 3075: Syntax error(missing operator) in query expression

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related