none
SQL statement loses one character in FROm clause RRS feed

  • Question

  • I have a number of SQL statements in the following form :
    TRANSFORM Count(lnkContactsCalls.Entry) AS CountOfEntry  SELECT tblMenuPostcodes.PostArea  FROM lnkContactsCalls LEFT JOIN tblmenuPostcodes ON lnkContactsCalls.PostCode = tblMenuPostcodes.Postcode  INNER JOIN tblMenuStaff ON lnkContactsCalls.StaffID = tblMenuStaff.Role  WHERE lnkContactsCalls.StaffID Like 'HE*' AND lnkContactsCalls.ContactDate Between #04/01/2017# and #06/30/2017# GROUP BY tblMenuPostcodes.PostArea PIVOT tblMenuStaff.UserName;

    The text from "FRM" to "Role" is all on one line.

    Putting the text in a new query using the SQL view works fine, but when running it from VBA, the last character in "Role" is dropped, making the syntax invalid.  It does not seem to matter whether there are more spaces at the end of the line or not.

    Any one come across this, and how did you fix it?  I'd rather not call a query if I can avoid it, but will do so if necessary.


    John Coyle Director, Praxis Data Solutions pty. Ltd.

    Saturday, November 25, 2017 5:44 AM

Answers

  • Any one come across this, and how did you fix it?  I'd rather not call a query if I can avoid it, but will do so if necessary.

    Hi John,

    I never use QueryDefs, but generate the sql-strings dynamically, as is your preference, I assume. Because of some long way back problems I systematically surround all the JOINs with brackets. Since then never met any further problems.

    So I agree with you that bracketing could be the solution.

    The sql-string would look like:

    TRANSFORM Count(lnkContactsCalls.Entry) AS CountOfEntry  SELECT tblMenuPostcodes.PostArea  FROM ((lnkContactsCalls LEFT JOIN tblmenuPostcodes ON lnkContactsCalls.PostCode = tblMenuPostcodes.Postcode)  INNER JOIN tblMenuStaff ON lnkContactsCalls.StaffID = tblMenuStaff.Role)  WHERE ...

    Imb.

    • Marked as answer by jco19389 Saturday, November 25, 2017 11:57 PM
    Saturday, November 25, 2017 11:11 AM

All replies

  • I may have found the answer - it seems to be that the brackets around the first JOIN statement were omitted.

    John Coyle Director, Praxis Data Solutions pty. Ltd.

    Saturday, November 25, 2017 6:13 AM
  • Any one come across this, and how did you fix it?  I'd rather not call a query if I can avoid it, but will do so if necessary.

    Hi John,

    I never use QueryDefs, but generate the sql-strings dynamically, as is your preference, I assume. Because of some long way back problems I systematically surround all the JOINs with brackets. Since then never met any further problems.

    So I agree with you that bracketing could be the solution.

    The sql-string would look like:

    TRANSFORM Count(lnkContactsCalls.Entry) AS CountOfEntry  SELECT tblMenuPostcodes.PostArea  FROM ((lnkContactsCalls LEFT JOIN tblmenuPostcodes ON lnkContactsCalls.PostCode = tblMenuPostcodes.Postcode)  INNER JOIN tblMenuStaff ON lnkContactsCalls.StaffID = tblMenuStaff.Role)  WHERE ...

    Imb.

    • Marked as answer by jco19389 Saturday, November 25, 2017 11:57 PM
    Saturday, November 25, 2017 11:11 AM
  • Thanks Imb - it is indeed the solution!  I now have some forty queries in a comprehensive report working as required

    John Coyle Director, Praxis Data Solutions pty. Ltd.

    Saturday, November 25, 2017 11:58 PM