I am tryinh to build a new SQL statement incorporating the string.

[wyldebag]

Hi all,
Any help on this would be greatly appreciated. I am trying to build a new sql statement for a query using a string from items selected in a list box on a form. I have the string in place and keep getting syntax errors for the new sql statement. The code is below.



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


Thanks in advance.
:banghead:JG.:banghead:

 

[Cronk]

Best to develop some debugging skill if you are generating SQL in code.

Put a break point on the line after you have generated the SQL string (F9 key) and run your code. When it stops, in the Immediate window below (or press Ctrl +G), type

? strSQL

Copy and paste the SQL text into new query and it will normally give you a clue about the problem.

In your case, strCriteria is a string and in a query the text has to have quotes around it.

What you need to have in your code is

IN ( ' " & strCriteria & " ' )"

I prefer to use

IN ( " & chr(34) & strCriteria &  chr(34) &" )"

in case there are in single quotes in the criteria eg Thomas O'Leary

 

[DavidAtWork]

you may also need to swap the order of the GROUP BY and WHERE clause.
Generally SQL statements will be structured like:
SELECT ...
FROM ...
WHERE ...
GROUP BY ...

David

 

[Cronk]

I'd overlooked the grouping bit. Actually the order is OK but the Where should be Having, IIRC.