MySQL Syntax Error from Query String in VBA

[raynman1972]

I have a pretty lengthy ODBC Connection MySQL query that I need to run through a loop in Access 2010 so that I can get aggregate some data. The query runs in MySQL Query Browser just fine, but when I parse it into individual strings for VBA, I get the following error:



"You have an error... syntax... use near 'USER_SCHEDULE_INTERVALS u JOIN(SELECT MAX(CREATION_DATE..."

strSQLa = "SELECT u.USER_NAME, s.SITE_ID" _
    & ",(CASE WHEN u.SCHEDULE_INTERVAL_TYPE_ID IN (38, 39, 43, 48, 49, 50, 51, 52, 53, 54, 55, 58) THEN -1 ELSE 1 END) * " _
    & "(CASE WHEN STR_TO_DATE(DATE_FORMAT(CONVERT_TZ(u.START_TIME, '+00:00','America/Los_Angeles'), '%H:%i'), '%H:%i') = STR_TO_DATE('08:15', '%H:%i')" _
    & "OR STR_TO_DATE(DATE_FORMAT(CONVERT_TZ(u.END_TIME, '+00:00','America/Los_Angeles'), '%H:%i'), '%H:%i') = STR_TO_DATE('08:15', '%H:%i') THEN 0.5 ELSE 1 END) AS HC" & vbNewLine

strSQLb = ",s.SITE_NAME AS SITE, uw.WORKGROUP_ID, w.WORKGROUP_NAME AS WORKGROUP" _
    & ",u.SCHEDULE_INTERVAL_TYPE_ID, t.NAME AS SCHED_TYPE, u.SCHEDULE_INTERVAL_STATUS_ID" _
    & ",COALESCE(u.DAY_OF_WEEK,(DATE_FORMAT(u.EFFECTIVE_START_DATE, '%w')-1)) AS DAY" _
    & ",u.EFFECTIVE_START_DATE, u.EFFECTIVE_END_DATE, STR_TO_DATE(DATE_FORMAT(CONVERT_TZ(u.START_TIME, '+00:00','America/Los_Angeles'), '%H:%i'), '%H:%i') AS START_TIME" _
    & ",STR_TO_DATE(DATE_FORMAT(CONVERT_TZ(u.END_TIME, '+00:00','America/Los_Angeles'), '%H:%i'), '%H:%i') AS END_TIME" _
    & "FROM USER_SCHEDULE_INTERVALS u " & vbNewLine

strSQLc = "JOIN(SELECT MAX(CREATION_DATE),USER_NAME,SITE_ID FROM USER_SITES" _
    & "WHERE (EFFECTIVE_START_DATE <= CURDATE())" _
    & "AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE >= CURDATE())" _
    & "GROUP BY USER_NAME,SITE_ID)us ON u.USER_NAME = us.USER_NAME" & vbNewLine

I have taken the parsed query and actually edited back out the VBA stuff and can confirm that the query is good. I am beginning to think that there is something that I am doing here that violates some Access VBA rule, anybody see anything glaringly obvious with what I have pasted above?

[orange]

One thing I noticed just briefly looking at your code:

strSQLc = "JOIN(SELECT MAX(CREATION_DATE),USER_NAME,SITE_ID FROM USER_SITES" _ & "WHERE (EFFECTIVE_START_DATE <= CURDATE())" _

You have not ended or started your strings with a space, so you get USER_SitesWHERE which is a syntax error.
When coding sql in vba as a series of concatenated strings, you should - by habit - end with a space or start with a space or both.

You should also do a debug.print of your sql string to ensure it is correct, before doing the actual execute of that SQL string.

[raynman1972]

One thing I noticed just briefly looking at your code:



You have not ended or started your strings with a space, so you get USER_SitesWHERE which is a syntax error.
When coding sql in vba as a series of concatenated strings, you should - by habit - end with a space or start with a space or both.

You should also do a debug.print of your sql string to ensure it is correct, before doing the actual execute of that SQL string.

Oh, for crap's sake! Forrest for the trees and all that. I guess I was tired when I got to this point of the code and then I just could not see this for anything. Works now, thanks!