Replace in WHERE Clause

[jambai]

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
Kumar

 

[traingamer]

Have you tried

SELECT * FROM dbo_Facility WHERE (REPLACE([NAME],',','') LIKE 'jambai[b]*[/b]'

i.e. using * rather than %

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[jambai]

Yes I did,

and I tried the below too

SELECT * FROM dbo_Facility WHERE (REPLACE([NAME],",","") LIKE 'jambai*'

Thanks
Kumar

 

[PHV]

Unbalanced parentheses, I guess.
You may try this (JetSQL syntax):
SELECT * FROM dbo_Facility WHERE Replace([NAME],',','') Like 'jambai*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jambai]

Thanks PHV,

But the parentheses are well in place. I tried with your Query too.

Data type mismatch in criteria expression was the error I got when I run these kind of queries

Thanks

 

[PHV]

And this ?
SELECT * FROM dbo_Facility
WHERE (Replace([NAME] & '',',','') & '') Like 'jambai*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jambai]

Hi,

I forgot to tell you that the table is a linked table from SQL Server.

Thanks

 

[jambai]

Thanks PHV.

It works.

Thank you so much

Jambai