How to specify Replace function in VBA

Question

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;*'; ;"

Martin · Accepted Answer · 2014-02-21 14:32:20Z

1

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;*';"

edited Feb 21, 2014 at 14:32

answered Feb 21, 2014 at 14:26

Martin

16.5k1 gold badge33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vuyy1182 Over a year ago

No luck. saying syntax error in string in query expression [CM] IS NOT NULL AND (';' & Replace([CM], '#', "") & ';') Like ';WR_2;'

Martin Over a year ago

I have just made an edit following another user comment to also reflect that you have two double-quotes in the last line, where you possibly mean two single-quotes.

Collectives™ on Stack Overflow

How to specify Replace function in VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related