[ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db
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 http://www.fusionlink.com -
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 -
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=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 -
Re: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db
I've run into many issues using CF-built queries on an Access db over the years. I recall one that had about a dozen doublequotes in it. Freaky! Have you tried including the [] around the field name just for fun? T [EMAIL PROTECTED] wrote: 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 -
