CODEWITHSUNDEEP
ms-accessvba
vuyy1182
vuyy1182

Reputation: 1676

How to specify Replace function in VBA

I'm using Replace function in SQL query, it is working in access sql query design mode but not working in access VBA. Throwing expected end of statement. I tried all the ways but not working. CM column has a string like this ;#WR_1;#WR_2;#WR_3;#WR_4;# and I'm trying to get value WR_2 where ever the string occurs in CM column

strSQL = "SELECT * FROM WT_table " & _
"WHERE [CM] IS NOT NULL " & _
"AND (';' & Replace([CM], '#', "") & ';') Like '*;WR_2;*'; ;"

Upvotes: 0

Views: 462

Answers (1)

Martin
Martin

Reputation: 16423

Just remove the second semi-colon (;) from the end of the last line - you have two of them, making it appear that there are multiple statements to be executed.

"AND (';' & Replace([CM], '#', "") & ';') Like '*;WR_2;*';"

In addition, as pointed out by simoco, you have a pair of double-quotes in your Replace function. I'm not certain whether you mean to have two single-quotes or not, but this would be the right code to use in that case:

"AND (';' & Replace([CM], '#', '') & ';') Like '*;WR_2;*';"

Upvotes: 1

Related Questions