REPLACE in WHERE CLAUSE  | Newbie | | Join Date: Feb 2007
Posts: 25
| | |
Hi,
Please let me know is it possible to achieve the below query in MS Access.
I have tried with " " (double quote) also in the replace. it doesn't help me.
SELECT * FROM dbo_Facility WHERE (REPLACE(REPLACE(REPLACE(NAME,',',''),'.',''),'''' ,'') LIKE 'jambai %')
Thanks
Jambai
|  | Site Addict | | Join Date: Feb 2007
Posts: 579
| | | re: REPLACE in WHERE CLAUSE
Quote:
Originally Posted by JAMBAI Hi,
Please let me know is it possible to achieve the below query in MS Access.
I have tried with " " (double quote) also in the replace. it doesn't help me.
SELECT * FROM dbo_Facility WHERE (REPLACE(REPLACE(REPLACE(NAME,',',''),'.',''),'''' ,'') LIKE 'jambai %')
Thanks
Jambai show me some data and description of thy tbl
|  | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,875
| | | re: REPLACE in WHERE CLAUSE
Unless this is a pass thru query you will need to change % to * which is the wildcard in Access.
Replace function looks ok but I would put NAME in square brackets and your closing bracket is in the wrong place. Try this ... -
-
SELECT * FROM dbo_Facility
-
WHERE (REPLACE(REPLACE(REPLACE([NAME],",",""),".",""),"'",""))
-
LIKE "jambai*")
-
Mary
| | Newbie | | Join Date: Feb 2007
Posts: 25
| | | re: REPLACE in WHERE CLAUSE
Quote:
Originally Posted by mmccarthy Unless this is a pass thru query you will need to change % to * which is the wildcard in Access.
Replace function looks ok but I would put NAME in square brackets and your closing bracket is in the wrong place. Try this ...
-
-
SELECT * FROM dbo_Facility
-
WHERE (REPLACE(REPLACE(REPLACE([NAME],",",""),".",""),"'",""))
-
LIKE "jambai*")
-
Mary I tried your query, it didn't work.
Data type mismatch in criteria expression was the error message. And the data type of the column is text. One more thing the table is a linked table from SQL 2000.
Thanks
jambai
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,875
| | | re: REPLACE in WHERE CLAUSE
Quote:
Originally Posted by JAMBAI I tried your query, it didn't work.
Data type mismatch in criteria expression was the error message. And the data type of the column is text. One more thing the table is a linked table from SQL 2000.
Thanks
jambai Did you copy and paste the code exactly?
Try this ... -
-
SELECT dbo_Facility.*,
-
(REPLACE(REPLACE(REPLACE(dbo_Facility.[NAME],",",""),".",""),"'",""))
-
FROM dbo_Facility
-
WHERE (REPLACE(REPLACE(REPLACE(dbo_Facility.[NAME],",",""),".",""),"'",""))
-
LIKE "jambai*")
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: REPLACE in WHERE CLAUSE
Quote:
Originally Posted by JAMBAI Hi,
Please let me know is it possible to achieve the below query in MS Access.
I have tried with " " (double quote) also in the replace. it doesn't help me.
SELECT * FROM dbo_Facility WHERE (REPLACE(REPLACE(REPLACE(NAME,',',''),'.',''),'''' ,'') LIKE 'jambai %')
Thanks
Jambai Replace will not work on versions of Access prior to 2K.
The '%' as a wildcard is only right in 2003 or later and where set to full ANSI compatibility mode.
| | Newbie | | Join Date: Feb 2007
Posts: 25
| | | re: REPLACE in WHERE CLAUSE
Quote:
Originally Posted by NeoPa Replace will not work on versions of Access prior to 2K.
The '%' as a wildcard is only right in 2003 or later and where set to full ANSI compatibility mode. Are you talking about the REPLACE in left side of the WHERE caluse. Because the Replace in the right side of the where clause is working fine
Thanks
Jambai
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: REPLACE in WHERE CLAUSE
I'm unclear what you'd be referring to as the left or right sides of the WHERE clause. Nor do I know which version of Access you're using.
I was simply mentioning, for reference, that Replace() doesn't exist in versions prior to 2K. If you're telling me you use Access 97 or earlier and it works for you then I'm surprised :confused:
| | Newbie | | Join Date: Feb 2007
Posts: 25
| | | re: REPLACE in WHERE CLAUSE
Quote:
Originally Posted by NeoPa I'm unclear what you'd be referring to as the left or right sides of the WHERE clause. Nor do I know which version of Access you're using.
I was simply mentioning, for reference, that Replace() doesn't exist in versions prior to 2K. If you're telling me you use Access 97 or earlier and it works for you then I'm surprised :confused:
I am using MS Access 2002 and the replace is working fine in the below query
SELECT * FROM dbo_Facility WHERE [NAME] Like Replace('jambai',',','')));
I am wondering ist possible to create the below query.
Please see the marked code, this was the one I referred as left side of the where clause
SELECT * FROM dbo_Facility WHERE (REPLACE([NAME],',','') LIKE 'jambai%'
Thanks
Jambai
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: REPLACE in WHERE CLAUSE
Ah, I understand what you mean now.
Yes.
...is the simple answer. SQL will deal with it on either side in that sense :)
| | Newbie | | Join Date: Feb 2007
Posts: 25
| | | re: REPLACE in WHERE CLAUSE
Quote:
Originally Posted by NeoPa Ah, I understand what you mean now.
Yes.
...is the simple answer. SQL will deal with it on either side in that sense :) Thanks NeoPa,
Please let me know how to achive the same query in MS ACCESS 2002
SELECT * FROM dbo_Facility WHERE (REPLACE([NAME],',','') LIKE 'jambai%'
Thanks
Jambai
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: REPLACE in WHERE CLAUSE
Quote:
Originally Posted by JAMBAI Thanks NeoPa,
Please let me know how to achive the same query in MS ACCESS 2002
- SELECT *
-
FROM dbo_Facility
-
WHERE (Replace([NAME],',','') Like 'jambai%'
Thanks
Jambai Exactly as you have it should work fine (except you should replace the % with a *).
| | Newbie | | Join Date: Feb 2007
Posts: 25
| | | re: REPLACE in WHERE CLAUSE
Quote:
Originally Posted by NeoPa Exactly as you have it should work fine (except you should replace the % with a *). Data type mismatch in criteria expression was the error message, when I tried that query
Yes I replaced the % with *.
Thanks
Kumar
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,419 network members.
|
