Syntax Error when running from Module

[SueAccess]

I get a syntax error when I run this query from an Access Module but I don't get an error when I run it as a straight query. Can anyone see what's wrong with it? I am using Access 2003

Dim sqlStmt As String

sqlStmt = "SELECT [CCC Companies].ESTBLMT_NO, SUM(subWeight) AS weight "
sqlStmt = sqlStmt + "FROM [CCC Companies], [SELECT ESTBLMT_NO, COUNT(*) * 10 AS subWeight "
sqlStmt = sqlStmt + "FROM CCCWords "
sqlStmt = sqlStmt + "WHERE Word IN ( Select word from CBCWords where vendor = '"
sqlStmt = sqlStmt + strVendor
sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO "
sqlStmt = sqlStmt + "UNION "
sqlStmt = sqlStmt + "SELECT ESTBLMT_NO,COUNT(*) * 25 AS subWeight "
sqlStmt = sqlStmt + "FROM CCCCleansedPhone "
sqlStmt = sqlStmt + "WHERE MID(STRIPPED_PHONE,1, 5) IN ( Select MID(STRIPPED_PHONE,1 ,5) FROM CBCCleansedPhone WHERE vendor_no = '"
sqlStmt = sqlStmt + strVendor
sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO "
sqlStmt = sqlStmt + "UNION "
sqlStmt = sqlStmt + "SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight "
sqlStmt = sqlStmt + "FROM CCCCleansedPhone "
sqlStmt = sqlStmt + "WHERE MID(STRIPPED_PHONE,1, 7) IN ( Select MID(STRIPPED_PHONE,1 ,7) FROM CBCCleansedPhone WHERE vendor_no = '"
sqlStmt = sqlStmt + strVendor
sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO "
sqlStmt = sqlStmt + "UNION "
sqlStmt = sqlStmt + "SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight "
sqlStmt = sqlStmt + "FROM CCCCleansedPostalCode "
sqlStmt = sqlStmt + "WHERE MID(STRIPPED_POSTAL,1, 6) IN ( Select MID(STRIPPED_POSTAL,1 ,6) FROM CBCCleansedPostalCode WHERE vendor_no = '"
sqlStmt = sqlStmt + strVendor
sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO "
sqlStmt = sqlStmt + "]. AS dupWeight "
sqlStmt = sqlStmt + "WHERE dupWeight.ESTBLMT_NO = [CCC Companies].ESTBLMT_NO "
sqlStmt = sqlStmt + "GROUP BY [CCC Companies].ESTBLMT_NO "
sqlStmt = sqlStmt + "HAVING SUM(subWeight) >= 60 "
sqlStmt = sqlStmt + "ORDER BY SUM(subWeight) DESC"

DoCmd.OpenForm "Show Probabilities", , , sqlStmt

Here is the code that runs ok in a straight query:

SELECT [CCC Companies].ESTBLMT_NO, SUM(subWeight) AS weight
FROM [CCC Companies], [SELECT ESTBLMT_NO, COUNT(*) * 10 AS subWeight
FROM CCCWords
WHERE Word IN ( Select word from CBCWords where vendor = '100024')
GROUP BY ESTBLMT_NO
UNION
SELECT ESTBLMT_NO, COUNT(*) * 25 AS subWeight
FROM CCCCleansedPhone
WHERE MID(STRIPPED_PHONE,1, 5) IN ( Select MID(STRIPPED_PHONE,1 ,5) FROM CBCCleansedPhone WHERE vendor_no = '100024')
GROUP BY ESTBLMT_NO
UNION
SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight
FROM CCCCleansedPhone
WHERE MID(STRIPPED_PHONE,1, 7) IN ( Select MID(STRIPPED_PHONE,1 ,7) FROM CBCCleansedPhone WHERE vendor_no = '100024')
GROUP BY ESTBLMT_NO
UNION
SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight
FROM CCCCleansedPostalCode
WHERE MID(STRIPPED_POSTAL,1, 6) IN ( Select MID(STRIPPED_POSTAL,1 ,6) FROM CBCCleansedPostalCode WHERE vendor_no = '100024')
GROUP BY ESTBLMT_NO
]. AS dupWeight
WHERE dupWeight.ESTBLMT_NO=[CCC Companies].ESTBLMT_NO
GROUP BY [CCC Companies].ESTBLMT_NO
HAVING SUM(subWeight)>=60
ORDER BY SUM(subWeight) DESC;

 

[RuralGuy]

The concantenation character in Access is the ampersand "&", not the plus sign "+". The "+" still works most of the time but you can get different results.

When I have these type of problems, I use a MsgBox of a Debug.Print to print out the string so I can look at it.

 

[SueAccess]

I changed the + to & and I still get a syntax error.

I think it might have something to do with this line:

sqlStmt = sqlStmt + "]. AS dupWeight "

The ]. is the close to the open of this line:

sqlStmt = sqlStmt + "FROM [CCC Companies], [SELECT ESTBLMT_NO, COUNT(*) * 10 AS subWeight "

The period after the ] is required when run as a query but maybe it causes a problem when run in a module. I tried removing the period but that didn't help.

 

[SueAccess]

Ok, never mind. The syntax error I got wasn't related to my query. It was related to

DoCmd.OpenForm "Show Probabilities", , , sqlStmt

Thanks a lot for your reply RuralGuy

 

[RuralGuy]

Glad I could help. Thanks for posting back with your success.