Re: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db

[Tom Chambers]

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 
- 






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

RE: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db

[Dan]

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




-
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

[Tom Chambers]

i have found numerous examples that show single quotes being used:



SELECT Replace([ItemDesired],'e','i') AS Expr1
FROM Orders

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





-
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

[Tom Chambers]

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 
- 





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

[ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db

[Dan]

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

-