SQL query no longer works after changing input from textbox to refedit

[captsubtext]

I setup a vba script to run a sql query using either "Param =" or "Param like" based on user selected input. The script worked fine when I used a textbox and just ran it against one value in the textbox.

So what I wanted to do next is allow the user to select a range of cells using the RefEdit control and run each query with the parameter input as the user selected values. This works fine when I select "Param =" but not with the "Param like" radio button.

Any thoughts as to why this is not working?

Here is my code:

    'Set the varables used for the SQL search string
    Dim sLikeOrEqual As String
    Dim sParam As Variant
    Dim sStartDate As String
    Dim sWildcard As String
    Dim lastrow As String
    Dim sEndDate As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    

Sub RunQuery_Click()

'This if statement is used to toggle between "Param =" or "Param Like" depending on their selection
If ParamLike.Value = True Then
    sLikeOrEqual = "param like "
    sWildcard = "%"
ElseIf ParamEquals.Value = True Then
    sLikeOrEqual = "param = "
    sWildcard = ""
End If

sStartDate = StartDate.Value
sEndDate = EndDate.Value

'Loop through each of the values selected by the user
For Each P In Range(SearchParameter.Value)
   sParam = P.Value
LoopThroughRange
Next

End Sub

Sub adocnnRoutine_SP(ByVal cnnstr As String, ByVal CallVal As Range, Optional CallHDR As Range)
'CallVal places the results in that one cell as a starting point Such as Sheet2.Range("A2")
'CallHDR is optional header placement point Such as Sheet2.Range("A1")

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

On Error GoTo CleanUp
cn.Open cnnstr
rs.Open "select callseq, eventdate, param from [IVR_DW_PHASE2].[dbo].[STAGING_log_events]" & " where " & sLikeOrEqual & "'" & sWildcard & sParam & sWildcard & "' and eventdate >= '" & sStartDate & "' and eventdate < '" & sEndDate & "'", cnnstr

'Sets the column headers
 If Not CallHDR Is Nothing Then
 With CallHDR
    For Each Field In rs.Fields
      .Offset(0, Offset).Value = Field.Name
      Offset = Offset + 1
    Next Field
  End With
 End If

CallVal.CopyFromRecordset rs

CleanUp:

Debug.Print Err.Description

cn.Close
Set rs = Nothing
Set cn = Nothing

End Sub

Public Sub LoopThroughRange()


'This function find the last row in from Sheet2 A1 and saves it as lastrow
With Sheets("Sheet2")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastrow = 1
    End If
End With
'adds one to last row so I can use it to populate sql data without overwriting existing cells
lastrow = lastrow + 1

'Pass the connection string, range and header values to the subroutine to run the SQl strings
adocnnRoutine_SP cnnstr:="connection string removed", CallVal:=Sheet2.Range("A" & lastrow), CallHDR:=Sheet2.Range("A1")
   

End Sub

[captsubtext]

It seems to be related to the length of the data I am searching for. If I use 3 numbers as the value it will run the search and return results. 10 numbers it seems to fail but 10+ characters is fine. I will have to do some more digging because its not the length of the search string it just cannot process above a certain number of integers.

[captsubtext]

I think I can just chalk this one up to inexperience with VB and excel. It seems to be a formatting issue. If I type in the phone numbers manually, the script will run fine and return all the results I need. If I copy and paste them from another workbook, theres an issue. Strange and frustrating, I thought I coded it wrong. Maybe someone else can get some use out of this code at least.

[Dimitrov]

There are a couple things I noticed about your code:

The first is that in the part of the SQL statement where you use the LIKE clause, you have

"'" & sWildcard & sParam & sWildcard & "'

It looks like you are missing a double quote on the end of this (to fully encase the single quote at the end).

The second thing is that when using the LIKE clause in the SQL statement, you are using single quotes (see my first point above) instead of square brackets. So when ParamEquals.Value = True, try this:

"[" & sWildcard & sParam & sWildcard & "]"

If you use either my first or second suggestion this might add some additional work to in terms of adding some more ampersands and double quotes for the code (e.g., eventdate >= ) immediately following this LIKE clause.

Third, I would suggest having two SQL statements. One in the case where ParamLike.Value = True, and one where ParamEquals.Value = True. This way, you will not try to mix and match the SQL statement in trying to cover both cases. A simple IF statement should do the trick.

I hope this helps.
_______________
[1] If this reply was helpful, click the star to the left.
[2] If this reply answered your question, mark this thread [SOLVED].

[captsubtext]

I like the suggestion about using 2 sql statements. It really helped clean up the code and remove a couple unnecessary variables.

Not sure why [ ] would be needed instead of ' ' in the like statement though.

+rep for the suggestions though thanks!