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.

 

[PWise]

try

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

 

[MazeWorX]

strSQL = "SELECT * FROM QueryName WHERE CodeField LIKE " & "*" & strCodeParam & "*" & ";"

Should do it

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Andrzejek]

Also, if [tt]strCodeParam[/tt] is something you get from users typing and they can type a single quote, you may want to do:

strSQL = "SELECT * from QueryName where CodeField like '*" & [blue]Replace([/blue]strCodeParam[blue], "'", "''")[/blue] & "'*"

Have fun.

---- Andy

 

[SQLJoe]

I'm afraid I've tried all of these with no luck. I had kept things simple at first just to give an example of what I'm trying to do, but now I've included my full code below. Basically, the code is designed to go through a form, reading each child_key and its corresponding batch_key field and run queries against each of four different "data file" letter tables to determine which table the child and batch number are listed in, then display that data file number on the form.

I know some of this can be done with loops, but I ran into issues with that too and don't have time to work through that now.

You will find each suggestion commented out in the appropriate section (under "Dynamically build query string"). When a solution is found, only ONE line will be there and the others will be removed.

Private Sub cmdFindLetters_Click()
Dim cmd As New ADODB.Command
Dim strChildKey As String
Dim strBatchKey As String
Dim rstMaterials As New ADODB.Recordset
Dim intloop As Integer
Dim strDF As String
Dim strDFVal As String
Dim intKeyNumloop As Integer
Dim intChildKeyNum As String
Dim intBatchKeyNum As String

On Error Resume Next

Call OpenDB

With cmd
.activeconnection = Application.CurrentProject.Connection
.Prepared = True
End With


intloop = 1
Do
'Loop through child key text fields, populating DF text fields in form.
intKeyNumloop = 1
Do While intKeyNumloop < 13
If intKeyNumloop = 1 And Not Len(Me.txtChildKey1) = 0 Then
strChildKey = Me.txtChildKey1
strBatchKey = Me.txtBatchKey1
ElseIf intKeyNumloop = 2 And Not Len(Me.txtChildKey2) = 0 Then
strChildKey = Me.txtChildKey2
strBatchKey = Me.txtBatchKey2
ElseIf intKeyNumloop = 3 And Not Len(Me.txtChildKey3) = 0 Then
strChildKey = Me.txtChildKey3
strBatchKey = Me.txtBatchKey3
ElseIf intKeyNumloop = 4 And Not Len(Me.txtChildKey4) = 0 Then
strChildKey = Me.txtChildKey4
strBatchKey = Me.txtBatchKey4
ElseIf intKeyNumloop = 5 And Not Len(Me.txtChildKey5) = 0 Then
strChildKey = Me.txtChildKey5
strBatchKey = Me.txtBatchKey5
ElseIf intKeyNumloop = 6 And Not Len(Me.txtChildKey6) = 0 Then
strChildKey = Me.txtChildKey6
strBatchKey = Me.txtBatchKey6
ElseIf intKeyNumloop = 7 And Not Len(Me.txtChildKey7) = 0 Then
strChildKey = Me.txtChildKey7
strBatchKey = Me.txtBatchKey7
ElseIf intKeyNumloop = 8 And Not Len(Me.txtChildKey8) = 0 Then
strChildKey = Me.txtChildKey8
strBatchKey = Me.txtBatchKey8
ElseIf intKeyNumloop = 9 And Not Len(Me.txtChildKey9) = 0 Then
strChildKey = Me.txtChildKey9
strBatchKey = Me.txtBatchKey9
ElseIf intKeyNumloop = 10 And Not Len(Me.txtChildKey10) = 0 Then
strChildKey = Me.txtChildKey10
strBatchKey = Me.txtBatchKey10
ElseIf intKeyNumloop = 11 And Not Len(Me.txtChildKey11) = 0 Then
strChildKey = Me.txtChildKey11
strBatchKey = Me.txtBatchKey11
ElseIf intKeyNumloop = 12 And Not Len(Me.txtChildKey12) = 0 Then
strChildKey = Me.txtChildKey12
strBatchKey = Me.txtBatchKey12
Else
Exit Sub
End If

'Loop through letter queries until match is found.
strDFVal = 1
Do
'Determine which letter DF to query
If strDFVal = 1 Then strDF = "7"
If strDFVal = 2 Then strDF = "10"
If strDFVal = 3 Then strDF = "17"
If strDFVal = 4 Then strDF = "24"
If strDFVal = 5 Then strDF = "25"

'Dynamically build query string
If strDF = "10" Or strDF = "17" Then
'strSQL = "SELECT * from qryDF" & strDF & " where n_child_key like '*" & strChildKey & "'*" & " and batch_key = " & "'" & strBatchKey & "'" & ";"
'strSQL = "SELECT * from qryDF" & strDF & " where n_child_key like " & "*" & strChildKey & "*" & " and batch_key = " & "'" & strBatchKey & "'" & ";"
'strSQL = "SELECT * from qryDF" & strDF & " where n_child_key like '*" & Replace(strChildKey, "'", "''") & "*'" & ";"
'strSQL = "SELECT * from qryDF" & strDF & " where n_child_key like """ & "*" & strChildKey & "*" & """ and batch_key = " & "'" & strBatchKey & "'" & ";"
ElseIf strDF = "7" Or strDF = "24" Then
'strSQL = "SELECT * from qryDF" & strDF & " where child_key like '*" & strChildKey & "'*" & " and batch_key = " & "'" & strBatchKey & "'" & ";"
'strSQL = "SELECT * from qryDF" & strDF & " where child_key like " & "*" & strChildKey & "*" & " and batch_key = " & "'" & strBatchKey & "'" & ";"
'strSQL = "SELECT * from qryDF" & strDF & " where child_key like '*" & Replace(strChildKey, "'", "''") & "*'" & ";"
'strSQL = "SELECT * from qryDF" & strDF & " where child_key like """ & "*" & strChildKey & "*" & """ and batch_key = " & "'" & strBatchKey & "'" & ";"
End If

'populate recordset
cmd.CommandType = adCmdText
cmd.commandtext = strSQL
Set rstMaterials = cmd.Execute()

'If records are returned, populate appropriate field on form.
If Not rstMaterials.EOF Then
If intKeyNumloop = 1 Then
Me.txtDF1 = rstMaterials.Fields("DF")
ElseIf intKeyNumloop = 2 Then
Me.txtDF2 = rstMaterials.Fields("DF")
ElseIf intKeyNumloop = 3 Then
Me.txtDF3 = rstMaterials.Fields("DF")
ElseIf intKeyNumloop = 4 Then
Me.txtDF4 = rstMaterials.Fields("DF")
ElseIf intKeyNumloop = 5 Then
Me.txtDF5 = rstMaterials.Fields("DF")
ElseIf intKeyNumloop = 6 Then
Me.txtDF6 = rstMaterials.Fields("DF")
ElseIf intKeyNumloop = 7 Then
Me.txtDF7 = rstMaterials.Fields("DF")
ElseIf intKeyNumloop = 8 Then
Me.txtDF8 = rstMaterials.Fields("DF")
ElseIf intKeyNumloop = 9 Then
Me.txtDF9 = rstMaterials.Fields("DF")
ElseIf intKeyNumloop = 10 Then
Me.txtDF10 = rstMaterials.Fields("DF")
ElseIf intKeyNumloop = 11 Then
Me.txtDF11 = rstMaterials.Fields("DF")
ElseIf intKeyNumloop = 12 Then
Me.txtDF12 = rstMaterials.Fields("DF")
End If

strDFVal = 5
End If

strDFVal = strDFVal + 1
Loop While strDFVal <= 5

intKeyNumloop = intKeyNumloop + 1
Loop
intloop = intloop + 1
Loop While intloop <= 5

End Sub

 

[PWise]

if this is a SQL Database try

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

 

[SQLJoe]

Thanks PWise, but unfortunately it's not an ADP file. Otherwise my life would be much easier!

 

[strongm]

> unfortunately it's not an ADP file

If you use ALike instead of Like then you can use % (but note there is a typo in both PWise's solutions - which are essentially the same - as discussed below)

> hover over the strSQL variable, it displays the parameter as "*'SC12345'*" but the query returns no records

Well, it wouldn't. You have to remember that the VBA IDE, to make clear that you are looking at a string, adds its own quote marks around the string. So when you hover and see

"*'SC12345'*"

the actual string is

*'SC12345'*

which is not what you need.

PWise's first post is closest - but there's a typo in it that again means you'll get no results:

[tt]strSQL = "SELECT * from QueryName where CodeField like '*" & strCodeParam & "'*"[/tt]

should actually be

[tt]strSQL = "SELECT * from QueryName where CodeField like '*" & strCodeParam & "[red]*'[/red]" [/tt]

 

[MazeWorX]

so for ease of read

strSQL = "SELECT *" _
       & " FROM qryDF" & strDF _
       & " WHERE child_key " _
       & " LIKE '*" & strChildKey & "*'" _
       & " AND ([batch_key] ='" & strBatchKey & "');"

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Andrzejek]

I would also suggest:

[green]'populate recordset[/green]
cmd.CommandType = adCmdText[blue]
  Debug.Print strSQL[/blue]
cmd.commandtext = strSQL
Set rstMaterials = cmd.Execute()

And when you run the code, you will get in Immediate Window your strSQL. Copy it and paste it here for us to see. When you see your SQL the way your data base gets it, you will probably be able to see your mistake.

Have fun.

---- Andy

 

[SQLJoe]

Thanks to all of your help, it looks like I've got the right syntax now, but I'm not quite out of the woods. This is what it resolves to in the immediate window:

SELECT * from qryDF24 where child_key like '*GC8871*' and batch_key = '218521';

Here's the catch (which leaves me befuddled...): After the command object executes, the recordset still shows rstMaterials.EOF = True, but if I copy the select statement into a new query and run it, it returns the expected row just fine. ??

 

[SQLJoe]

I should add that, before I realized I needed to use the 'Like' with the wildcard and was still running sql without it, the command object and recordset were returning data just fine and I haven't changed them since. I'm looking things over again.

 

[Andrzejek]

Should you have:
[tt]and batch_key = '218521'[/tt]
or
[tt]and batch_key = 218521[/tt] ?

In other words, is the field [tt]batch_key[/tt] a text or a number field? And you may NOT need the [tt];[/tt] at the end of your SQL.

Have fun.

---- Andy

 

[SQLJoe]

batch_key is a string: nvarchar(100). (I know earlier I said it's not an ADP file, but I do connect with a sql db via odbc, in addition to local Access tables.)

 

[PHV]

Anyway, with ADODB the wildcard is %, not *

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SQLJoe]

Thanks PHV! That ended up being why EOF was still true; ADODB interpreted * as part of the string, and so it found no records, but the query editor interpreted it as a wilcard, so it did. I changed the character from * to % in code and that solved it. Thanks again.

 

[strongm]

>I know earlier I said it's not an ADP file, but I do connect with a sql db via odbc, in addition to local Access tables

Always important to include all the relevant info ...

 

[TheAceMan1]

How are ya SQLJoe . . .

I ran into this thread at about the 9th post, and have been following it since. Although there's been alot of syntax problems in the early stages of this thread, Id like to introduce you to the method that makes the [blue]Like[/blue] statement or [blue]string comparsions[/blue] easy for me. I don't have to remember triple/double quotes or where to put them ... before or end, or anything else. I believe your already aware ... there's a difference between SQL in VBA and the SQL view in the query pane of access. SQL in VBA [purple]has to be concatenated properly![/purple] This is where subStrings/enbedded strings come into play in SQL.

The method involves the following string declaration and assignment:

[blue]   Dim DQ as string [green]'DQ stands for Double Quotes[/green]

   DQ = """"[/blue]

Once done ... you use DQ where you would normally concatenate quotations ... start and end ... just like in the SQL view of the SQL window. Using your example in your post origination (assuming strCodeParam = "[purple]*SC12345*[/purple]":

[blue]SQL in SQL view of the query pane. Looks Like:
 ... where (CodeField like "*SC12345*");

In VBA it would be:
" ... where ([CodeField] like " & [purple][b]DQ[/b][/purple] & strCodeParam & [purple][b]DQ[/b][/purple] & ");"

If strCodeParam = "[purple][b]SC12345[/b][/purple]" then in VBA it would be:
" ... where ([CodeField] like " & [purple][b]DQ[/b][/purple] & "*" & strCodeParam & "*" & [purple][b]DQ[/b][/purple] & ");"[/blue]

Note: DQ also takes care of single quotes in strings.

Hope this helps.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

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

 

[PHV]

DQ also takes care of single quotes in strings
But not of double quotes, like 3"5 for example.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

PHV . . .

That is correct and a [blue]unique occurrence[/blue] where I'd have to use:

[blue]   Like "*3""*"[/blue]

See Ya! . . . . . .

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