Syntax error in SQL statement?

[ClifCamp]

I recently made some unrelated changes to my db and some code I had to update counts in a table worked perfectly but now doesn't work. Err.description tells me it's a syntax error and it occurs right after the SQL for counting distinct employee IDs. I have tried everything I know to figure out what it doesn't like and have failed. The code loops through a table with queries listed and first gets a DCount for each query and writes to the table because I don't need distinct-this still works. It then loops through the same queries and gets a distinct count- this section now fails. This code used to work perfectly until I added some additional tables which the list of queries pull data from. If someone has some insight, it would be much appreciated. Code is below:

Dim rstQueryList As DAO.Recordset
Dim rstCountData As DAO.Recordset
Dim strSQLQueryList As String
Dim strQueryName As String
Dim strSQLQueryData As String
Dim RecordCount As Integer
Dim strSQL As String
Dim qdf As QueryDef


strSQLQueryList = "SELECT * FROM tblQueries;"

Set db = CurrentDb()
Set rstQueryList = db.OpenRecordset(strSQLQueryList)
rstQueryList.MoveFirst
Do While Not rstQueryList.EOF 'loops through the list of queries
strQueryName = rstQueryList!Query
rstQueryList.Edit
rstQueryList!RecCnt = DCount("*", strQueryName) 'write the record count count to the table
rstQueryList.Update
rstQueryList.MoveNext
Loop
'count distinct affected employees

rstQueryList.MoveFirst
Do While Not rstQueryList.EOF 'loops through the list of queries
strQueryName = rstQueryList!Query

strSQL = "SELECT Count([Employee ID]) AS AffEmps FROM (SELECT DISTINCT [Employee ID] FROM " & strQueryName & ");"
Set rstCountData = db.OpenRecordset(strSQL)
MsgBox Err.Description 'The error seems to happen here
'MsgBox strQueryName & " = " & rstCountData!AffEmps

rstQueryList.Edit
rstQueryList!AffEmpCnt = rstCountData!AffEmps 'write the number of affected employees to the table
rstQueryList.Update
rstCountData.Close
rstQueryList.MoveNext
Loop
rstQueryList.Close
rstCountData.Close

Me.Requery

db.Close

DoCmd.SetWarnings True
DoCmd.Hourglass False
MsgBox "Download successfully imported.", vbExclamation, "Success!"

 

[Remou]

What does strQueryName contain? You can set a breakpoint and check, or you can use debug.print, which means you can paste into the query window to see what is wrong. Note the addition of square brackets, it is safer when you do not know the query name.

strSQL = "SELECT Count([Employee ID]) AS AffEmps FROM (SELECT DISTINCT [Employee ID] FROM [" & strQueryName & "]);"
Debug.Print strSQL

You should avoid set warnings. It can hide real problems. If you do not want to okay every query, you can use CurrentDB.Execute strSQL.

http://lessthandot.com

 

[vbajock]

I agree with Remou, you are probably pulling a query with a space in the name or something. I bracket all variables inserted into SQL statement field and table name as a habit.