Yes Charlie, and thank you for refreshing this thread. When the problem first occurred for me I did think it was a syntax problem (of mine) and so that is where I looked for an answer. Ultimately I also found an explanation similar to yours within a Microsoft blog from an Access Dev. Engineer. He explained it somewhat like your explanation below that this (the REPLACE) function is internal to Access only but didn't go into the Security issue that you did. He basically just said "It cannot be used outside of Access."
I did not bother to test if from within Access at the time since I didn't need a solution there, I needed it on the outside, within the CF interface. But ironically that's exactly where I did end up using the very same REPLACE function. I took the advice of Rob Munn with the San Diego CFUG (SDCFUG)who suggested I simply add an Ordering column to the db. Overall this was so obvious and too simple. So what I ended up doing was this: 1. from within Access I did a search on the mTitle column for all records with a leading The. There were only a couple dozen. 2. I created a new column oTitle and from WITHIN Access I used the REPLACE function and copied all the records to the oTitle column stripping out the instances of The. 3. I then modified the data entry form to include the oTitle field and using a little JS magic I copy the mTitle entry to the oTitle field stripping off any leading "The" So at the point of adding a new movie to the db there is almost no cost in controlling how it is ordered. Another advantage here is that the user can, of course, manually edit the oTitle value to create "grouped" sets of movies. For example, my client wanted to group all the James Bond movies together. Now all he has to do is put 007 in front of the movie Title in the oTitle field: 007 Die Another Day. The oTitle field is not displayed anywhere in the web interface. 4. And I of course modified the INSERT and UPDATE queries to include the new oTitle field. Thanks again to everyone for their input. Sometimes [I] get so close to the trees I fail to see the forest--or the big picture, and lose track of my proper goal (by getting hung up on technique and detail). Dan Kaufman -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Arehart Sent: Sunday, December 03, 2006 12:11 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db Dan? Any thoughts on this? /Charlie http://www.carehart.org/blog/ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Arehart Sent: Monday, November 27, 2006 10:39 AM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db 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 ------------------------------------------------------------- ------------------------------------------------------------- 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=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 -------------------------------------------------------------
