none
Error in From clause in VBA, not in query editor... RRS feed

  • Question

  • I have a piece of code in which I modify a report's recordsource and then try to load the report.

    I have debugged the generated SQL and it evaluates to:

    TRANSFORM Sum(qryReqProduct.TotalQty) AS SumOfTotalQty
    SELECT qryReqProduct.[Product Desc], Sum(Switch([qryReqProduct]![Sorter]=2,0,[qryReqProduct]![Sorter]<>2,[qryReqProduct]![TotalQty])) AS [Total Of ProdQty]
    FROM qryReqProduct
    WHERE qryReqProduct.[Output] ='Regular'
    GROUP BY qryReqProduct.[Product Desc]
    PIVOT qryReqProduct.[Store Name] In('X','Y','Z');

    This works fine in the query editor but I get a "syntax error in FROM clause" message in VBA.

    Any suggestions?


    Thanks for your help!!

    Monday, December 26, 2016 7:07 PM

Answers

  • In the Immediate window, debug.print your variable so you get the EXACT sql.

    Create a new query. Cancel the "select tables" dialog, switch to SQL view and paste that sql statement. Try switching to design view, and the parser will point out the problem. My guess is a missing [space].


    -Tom. Microsoft Access MVP

    Monday, December 26, 2016 9:11 PM

All replies

  • In the Immediate window, debug.print your variable so you get the EXACT sql.

    Create a new query. Cancel the "select tables" dialog, switch to SQL view and paste that sql statement. Try switching to design view, and the parser will point out the problem. My guess is a missing [space].


    -Tom. Microsoft Access MVP

    Monday, December 26, 2016 9:11 PM
  • While impossible to tell what the VBA is doing because it’s not displayed, my guess is that you are missing a space character at the end of one of the lines.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Monday, December 26, 2016 9:14 PM