REPLACE in WHERE CLAUSE

[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

[vijaydiwakar]

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

[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

[JAMBAI]

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

[MMcCarthy]

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*")

[NeoPa]

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.

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

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

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

[JAMBAI]

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

[NeoPa]

Ah, I understand what you mean now.
Yes.
...is the simple answer. SQL will deal with it on either side in that sense :)

[JAMBAI]

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

[NeoPa]

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 *).

[JAMBAI]

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