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