I am having trouble building a new SQL statement incorporating a string as criteria

[wyldebag]

I am trying to build a new sql statement from items i am selecting from a listbox on a form. I have built the string for variables but am having trouble with the syntax on the visual basic code for the new sql statement. i am receiving a "Run-time error '3075': syntax error (missing operator) in query expression."



Below is my code

strSQL = "SELECT JG_tbl_LMEMP.DEPT_CODE" & _
" FROM JG_tbl_LMEMP" & _
" Group by JG_tbl_LMEMP.DEPT_CODE" & _
" Where JG_tbl_LMEMP.DEPT_Code IN(" & strCriteria & ")"


qdf.SQL = strSQL

[JoeM]

A few things:
- When using GROUP BY, the WHERE clause comes before the GROUP BY clause (if using Aggregated calculations in your criteria, you would put that in a HAVING clause after the GROUP BY clause).
- What is the value of "strCriteria"?
- If the DEPT_Code is a text field, the entries need to be surrounded by double quotes (for Access queries) or single quotes (for native SQL queries).
So if building an Access query, and your DEPT_Code field is Text data type, I would write that line like this:
" Where JG_tbl_LMEMP.DEPT_Code IN(" & Chr(34) & strCriteria & Chr(34) & ")"
(Chr(34) is the ASCII representation of double quotes).

A good little trick that I use is I first create an example of the query I an trying to build in Query Builder, then change to SQL View and copy and paste that code out to somewhere where I can reference it. This is the code that I am trying to build with my VBA code.
Then, in the VBA code, before assigning your SQL code to a query, you can display it in a Message Box to see if it matches the code you built (to make sure that you have done everything correctly).