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 -------------------------------------------------------------
