Access changes single quote with double??

[Idee]

I am working in Cold fusion and Access. I have a list storing countrycodes like AU,NZ etc. I store it in attributes.cntrylist I listqualify this varibale so as to make it like 'AU','NZ' Then run a query like this:

<cfparam name="attributes.cntrylist" default="">
<cfset cntrylist = listqualify (attributes.cntrylist,"'",",",'ALL')>

<cfquery name="getCountry" datasource="getCountry">
SELECT country,code
FROM CountryCodes
WHERE code in (#attributes.cntrylist#)
ORDER BY Country
</cfquery>

This query doesn't work as in error message it displays

SELECT country,code FROM CountryCodes WHERE code in (''AU'',''NZ'') ORDER BY Country

but when I output the variable attributes.cntrylist, it shows 'AU','NZ'.

I don't know why single quotes are being converted to double quotes in query.

Any help is appreciated. Thanks...

 

[lynchg]

That is what Access does. It can read single quotes and interprets them the same as double quotes, but when you save a query in Access it will convert them all to double quotes.

 

[Idee]

Thanks for your feedback but how can I overcome this ..

My select query should be something like this...

SELECT country,code
FROM CountryCodes
WHERE code in ('NZ','AU')
ORDER BY Country

It works fine in Access directly but when this query is executed through code, it fails as it becomes

SELECT country,code
FROM CountryCodes
WHERE code in ("NZ","AU")
ORDER BY Country


Any suggestions? Thanks

 

[TonyJollans]

Hi Inderdip,

Sorry, I don't know ColdFusion but I don't understand the problem. The query should work in Access whether you have double or single quotes. What excatly goes wrong?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.