Ok, this is ugly, but it works... cfset ziplist= "98404,98405,98408"
CFQUERY NAME="getZip" DATASOURCE="sometestdb" Select * >From tblZip Where ZIP in (<cfloop list="#ziplist#" index="element">'#element#',</cfloop>); /CFQUERY cfdump var="#getZip#" -----Original Message----- From: David Schmidt [mailto:[EMAIL PROTECTED]] Sent: Friday, December 14, 2001 1:02 PM To: CF-Talk Subject: Lists, Access, & the IN sql operator - Help needed. Hi all, I have a little problem with Lists, Access & the sql "IN" operator and was curious if anyone had come across this before. Apparently, CF drops in extra single quotes around list items output inside sql statements. Anyone have a work around? Thanks In Advance, David Schmidt Here is the code (keep in mind that it is simply test code and not a real app): cfset ziplist= chr(39) & "98404" & chr(39) & "," & chr(39) & "98405" & chr(39) (comment: CHR(39) = "'") cfoutput#ziplist#/cfoutput (comment: This displays '98404','98405' and that is what I want) CFQUERY NAME="getZip" DATASOURCE="testlistdb" Select * >From tblZip Where ZIP in (#ziplist#); /CFQUERY cfdump var="#getZip#" (comment: This never happens because of the sql error below) ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ZIP in (''98404'',''98405'')'. SQL = "Select * From tblZip Where ZIP in (''98404'',''98405'');" Please note the double (single quotes). ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
