VBA/SQL Join Error

[jgelpi16]

I think I just need another set of eyes taking a look at this. I'm executing the below SQL in a module. I keep getting a "Syntax error in JOIN operation." error with "QRY_SEL_D100_DATA_TM_D.GROUP" highlighted at the beginning of the SELECT statement. Any suggestions would be much appreciated.

    lngCatID = 9
    dtMonthBeg = funDataDate()
    strMonth = UCase(Format(dtMonthBeg, "mmm"))
    lngMonthIndex = DLookup("LOOKUP_ID", "TBL_LOOKUP_VALUES", "[LOOKUP_DESC] = '" & strMonth & "'")
    strLevel = "D"    
mySQL = "INSERT INTO TBL_RESULTS ( [GROUP], REGION, DISTRICT, CATEGORY, LEVEL_FLAG, RESULTS_MONTH, RESULTS_MONTH_BEG, " & _
        "MONTH_RESULTS, YTD_RESULTS ) " & _
        "SELECT QRY_SEL_D100_DATA_TM_" & strLevel & ".GROUP, QRY_SEL_D100_DATA_TM_" & strLevel & ".REGION, " & _
        "QRY_SEL_D100_DATA_TM_" & strLevel & ".DISTRICT, " & lngCatID & ", '" & strLevel & "', " & lngMonthIndex & ", #" & dtMonthBeg & "#, " & _
        "IIf(Month([QRY_SEL_D100_DATA_TM_" & strLevel & "]![Month_Begin])=9," & _
        "([QRY_SEL_D100_DATA_TM_" & strLevel & "]![D100_YTD_TY])/([QRY_SEL_D100_DATA_TM_" & strLevel & "]![STORE_SALES_YTD_TY])," & _
        "([QRY_SEL_D100_DATA_TM_" & strLevel & "]![D100_YTD_TY]-[QRY_SEL_D100_DATA_LM_" & strLevel & "]![D100_YTD_TY])/" & _
        "([QRY_SEL_D100_DATA_TM_" & strLevel & "]![STORE_SALES_YTD_TY]-[QRY_SEL_D100_DATA_LM_" & strLevel & "]![STORE_SALES_YTD_TY])), " & _
        "[QRY_SEL_D100_DATA_TM_" & strLevel & "]![D100_YTD_TY]/[QRY_SEL_D100_DATA_TM_" & strLevel & "]![STORE_SALES_YTD_TY] " & _
        "FROM QRY_SEL_D100_DATA_TM_" & strLevel & ", QRY_SEL_D100_DATA_LM_" & strLevel & " INNER JOIN QRY_SEL_D100_DATA_LM_" & strLevel & " " & _
        "ON QRY_SEL_D100_DATA_TM_" & strLevel & ".DISTRICT = QRY_SEL_D100_DATA_LM_" & strLevel & ".DISTRICT;"
    CurrentDb.Execute mySQL, dbFailOnError

[pbaldy]

I would use this method to check the finished SQL:

http://www.baldyweb.com/ImmediateWindow.htm

offhand, you probably need to bracket group everywhere since its a reserved word. Post the SQL here if you don't see the problem.

[jgelpi16]

Of course! I didn't think of the fact that Group is a reserved word! Thank you.

[pbaldy]

No problem! It was fresh in my mind because I recently worked on a client db and ran into that, as they used group as a field name.

[jgelpi16]

I would use this method to check the finished SQL:

http://www.baldyweb.com/ImmediateWindow.htm

offhand, you probably need to bracket group everywhere since its a reserved word. Post the SQL here if you don't see the problem.

Unfortunately this didn't work. I'm still experiencing the same error. When I copy/paste the SQL into a query this is what I experience...

SQL:

INSERT INTO TBL_RESULTS ( [GROUP], REGION, DISTRICT, CATEGORY, LEVEL_FLAG, RESULTS_MONTH, RESULTS_MONTH_BEG, MONTH_RESULTS, YTD_RESULTS ) SELECT QRY_SEL_D100_DATA_TM_D.[GROUP], QRY_SEL_D100_DATA_TM_D.REGION, QRY_SEL_D100_DATA_TM_D.DISTRICT, 9, 'D', 17, #9/1/2014#, IIf(Month([QRY_SEL_D100_DATA_TM_D]![Month_Begin])=9,([QRY_SEL_D100_DATA_TM_D]![D100_YTD_TY])/([QRY_SEL_D100_DATA_TM_D]![STORE_SALES_YTD_TY]),([QRY_SEL_D100_DATA_TM_D]![D100_YTD_TY]-[QRY_SEL_D100_DATA_LM_D]![D100_YTD_TY])/([QRY_SEL_D100_DATA_TM_D]![STORE_SALES_YTD_TY]-[QRY_SEL_D100_DATA_LM_D]![STORE_SALES_YTD_TY])), [QRY_SEL_D100_DATA_TM_D]![D100_YTD_TY]/[QRY_SEL_D100_DATA_TM_D]![STORE_SALES_YTD_TY] FROM QRY_SEL_D100_DATA_TM_D, QRY_SEL_D100_DATA_LM_D INNER JOIN QRY_SEL_D100_DATA_LM_D ON QRY_SEL_D100_DATA_TM_D.DISTRICT = QRY_SEL_D100_DATA_LM_D.DISTRICT;

[pbaldy]

If I'm reading that right that you have

FROM TableA, TableB INNER JOIN TableB ON...

It should be

FROM TableA INNER JOIN TableB ON...

[jgelpi16]

That did it. Thank you!

[pbaldy]

Happy to help!