Could it be that you need to 'PreserveSingleQuotes()' in the SQL statement
i.e.
<CFQUERY name="getstuff" datasource="ACCESSDATABASE">
SELECT item_id
FROM inv_mast
WHERE partnumber NOT
IN(#PreserveSingleQuotes(QuotedValueList(getproducts.partnumber))#) AND
manufacturer_id = 4
</CFQUERY>
I've fallen for that more times than I care to remember....
-----Original Message-----
From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]]
Sent: Friday, 1 June 2001 4:08 AM
To: CF-Talk
Subject: RE: Is this an Access limitation ?
> The code below throws an error whenever I try to run it... What
> am I doing wrong ?
>
> Thanks in Advance...
>
> <CFQUERY name="getproducts" datasource="SQL7DATABASE" >
> SELECT partnumber
> FROM products
> WHERE manufacturer_id = 4
> </CFQUERY>
>
> <CFQUERY name="getstuff" datasource="ACCESSDATABASE">
> SELECT item_id
> FROM inv_mast
> WHERE partnumber NOT
> IN(#QuotedValueList(getproducts.partnumber)#) AND manufacturer_id = 4
> </CFQUERY>
Firstly, what datatype is PartNumber in Access?
If it's a number, then use ValueList(PartNumber) rather than
QuotedValueList(PartNumber)
If it's not, put the SQL to the display before you run the SQL to make sure
it "should" run - try it in Access if needs be
Also, check the getProducts.RecordCount - if it's 0 then the SQL will break
no matter what database engine you throw it at...
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
