Concatenating a variable into dynamic SQL

[SQLJoe]

I need to dynamically build a select statement in code that concatenates a string parameter variable. I'm having issues getting the quotes and wildcards placed correctly. For example, I need to build a select statement such as

Select * from QueryName where CodeField like "*SC12345*"

I have to use "like" with the wildcards because the code may only be part of a longer string in the underlying table. Getting my syntax right to build the equivalent for "*SC12345*" is tricky, especially since it also needs single quotes. I've tried the following with no luck;

strSQL = "SELECT * from QueryName where CodeField like """ & "*'" & strCodeParam & "'*" & """ & ";"

When I set a break point and hover over the strSQL variable, it displays the parameter as "*'SC12345'*" but the query returns no records, though I have verified that it should be returning data for this.

I've tried some other similar syntaxes but still with no luck. Any help is appreciated.

 

[Andrzejek]

Since your DQ has the same value, I would use a Constant instead. This way declaration and assigning the value is just one line, and you cannot assign anything else to it.

Have fun.

---- Andy

 

[TheAceMan1]

Andrzejek . . .

Actually I always set it up as a [blue]public constant[/blue] in a standard module:

[blue]Public Const DQ As String = """"[/blue]

Now its globally available ...


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SQLJoe]

Thanks AceMan, that's a great technique!