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