Dan, the problem you've hit is not a syntax problem but rather a security
issue Access, it seems. For instance, if you tried that SQL statement WITHIN
Access, did it work? It did for me, yet trying to pass it in from outside
(from CF), it fails. And it's not a datasource setup problem. I tried both
the ODBC Socket and the "Microsoft Access" driver in CFMX. Both failed. 

Doing some digging, I've found that you've run into is something that many,
many (indeed, mostly outside of CF) have hit as well. Access has implemented
a security "sandbox" for certain functions to prevent their use from outside
of Access itself. 

There are several references that describe it. Do a search inside Access'
help for "sandbox" to see many. Still, I tried all the things that were
offered there and in other web sites to configure things to work, and none
would. I tried setting the security to "Low" as described in the help, but
no dice. I even tried a Registry fix discussed here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;294698&Product=acc


To no avail. I even tried converting an Access 2000 DB to Access 2003 and it
still would not work. And I'll add that I stopped and restarted both Access
and CF (and the CF datasource services) after the changes, and even reloaded
a test page in a new browser window (to avoid seeing a cached result). And
with none could I get it to work.

I found some references suggestion that you create a wrapper function or
module to get things working, but I'm not as familiar with creating those in
Access let alone calling them from CF, so I'll leave this info with you for
your consideration.

Teddy's idea may be the better solution, if indeed there's no reason for the
data to exist in its current state.

If it must, I'll point out one last "out of the box" solution: though it's
preferable to let the database do database work, in this case where you're
stumped, you could pull the data into a CF query (as is) and use CF
functions to manipulate it, then use a query of queries to do the sort that
you prefer on the manipulated result. May not perform well, but sometimes
you need function over form. :-)

/Charlie
http://www.carehart.org/blog/  

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Sunday, November 26, 2006 8:01 PM
To: discussion@acfug.org
Subject: RE: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db

Sorry, the double quotes in the query statements are simply a typo on my
part. I didn't copy and paste the actual query,  I simply retyped it as I
wrote the email and out of bad habit used " quotes.

The quotes ' or " is not the problem.  This query and/or using the REPLACE
function works perfectly well on all my SQL datasources.  This particular
site (still) has an Access db. And therein lies the rub.

Yes, I could, and probably should, "upsize" the Access mdb to SQL-2000, but
for right now that's more trouble than simply making the query behave.

So, any ideas on how to use the REPLACE function in a query on an Access db?
Or, baring that, how would you create a similar end result of ORDER BY the
mTitle column ignoring "the" if it is the first word in a movie title?

Thanks,

Dan


-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Chambers
Sent: Sunday, November 26, 2006 4:45 PM
To: discussion@acfug.org
Subject: Re: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db

did you try single quotes?



[EMAIL PROTECTED] wrote:
>
> I have an Access datasource for movie listings. mTitle is the 
> column/field for the movie's Title. I need to output the records 
> ordered by mTitle, however I need to ignore "the" when it is the first 
> word.
>
>  
>
> For example: The Big Lebowski should be ordered by B in Big, not T in The.
>
>  
>
> My solution was to create an alias column in my query wherein I 
> REPLACEd "the" with "" and then ORDER BY the alias column.
>
>  
>
> SELECT
>
>       mTitle,
>
>       REPLACE(mTitle, "the", "") AS titleOrder
>
> FROM
>
>       movieTable
>
> ORDER BY
>
>       titleOrder
>
>  
>
> However, when I run this I get an error that the function REPLACE is
> undefined:
>
>  
>
> [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC 
> Microsoft Access Driver] Undefined function 'REPLACE' in expression.
>
>  
>
> I have "Googled" for an answer to my problem (till I'm blue in the
> face) but can't find anything HELPFUL.
>
>  
>
> Can anyone help OR suggest a different solution?
>
>  
>
> Thanks,
>
>  
>
>  
>
> Dan Kaufman
>
>  
>
>
> -------------------------------------------------------------
> To unsubscribe from this list, manage your profile @ 
> http://www.acfug.org?fa=login.edituserform
>
> For more info, see http://www.acfug.org/mailinglists Archive @ 
> http://www.mail-archive.com/discussion%40acfug.org/
> List hosted by FusionLink <http://www.fusionlink.com>
> -------------------------------------------------------------




-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform

For more info, see http://www.acfug.org/mailinglists Archive @
http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------





-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=gin.edituserform

For more info, see http://www.acfug.org/mailinglists Archive @
http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------






-------------------------------------------------------------
To unsubscribe from this list, manage your profile @ 
http://www.acfug.org?fa=login.edituserform

For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------



Reply via email to