469,342 Members | 5,481 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

REPLACE in WHERE CLAUSE

JAMBAI
28
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
Feb 24 '07 #1
12 8537
vijaydiwakar
579 512MB
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
Feb 24 '07 #2
MMcCarthy
14,534 Expert Mod 8TB
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 ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT * FROM dbo_Facility 
  3. WHERE (REPLACE(REPLACE(REPLACE([NAME],",",""),".",""),"'",""))  
  4. LIKE "jambai*")
  5.  
Mary
Feb 24 '07 #3
JAMBAI
28
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 ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT * FROM dbo_Facility 
  3. WHERE (REPLACE(REPLACE(REPLACE([NAME],",",""),".",""),"'",""))  
  4. LIKE "jambai*")
  5.  
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
Feb 24 '07 #4
MMcCarthy
14,534 Expert Mod 8TB
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 ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT dbo_Facility.*, 
  3. (REPLACE(REPLACE(REPLACE(dbo_Facility.[NAME],",",""),".",""),"'",""))
  4. FROM dbo_Facility 
  5. WHERE (REPLACE(REPLACE(REPLACE(dbo_Facility.[NAME],",",""),".",""),"'",""))  
  6. LIKE "jambai*")
Feb 26 '07 #5
NeoPa
32,183 Expert Mod 16PB
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.
Feb 26 '07 #6
JAMBAI
28
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
Feb 26 '07 #7
NeoPa
32,183 Expert Mod 16PB
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:
Feb 26 '07 #8
JAMBAI
28
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
Feb 28 '07 #9
NeoPa
32,183 Expert Mod 16PB
Ah, I understand what you mean now.
Yes.
...is the simple answer. SQL will deal with it on either side in that sense :)
Feb 28 '07 #10
JAMBAI
28
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
Feb 28 '07 #11
NeoPa
32,183 Expert Mod 16PB
Thanks NeoPa,

Please let me know how to achive the same query in MS ACCESS 2002

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM dbo_Facility
  3. WHERE (Replace([NAME],',','') Like 'jambai%'
Thanks
Jambai
Exactly as you have it should work fine (except you should replace the % with a *).
Feb 28 '07 #12
JAMBAI
28
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
Feb 28 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by A.V.C. | last post: by
8 posts views Thread by Captain Dondo | last post: by
2 posts views Thread by Jim.Mueksch | last post: by
5 posts views Thread by pwiegers | last post: by
6 posts views Thread by JackpipE | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.