Navigation:  Articles > Oct-1997 >

Tip: Like, You Know

Previous pageReturn to chapter overviewNext page

Darius Lamanauskas          
 
I find myself writing a lot of code to build SQL statements on the fly, based on my users' input. I'd like to be able give my users the ability to use wild cards -- which means using the SQL statement I create has to use the Like operator. However, on average, a query using Like will run slower than a query using the equal sign operator. So, I created the UseLike function to let me know when I need to use "Like."
 

Function UseLike(strValue As String) As  Integer

' Purpose: define whether to use "Like" in SELECT

'Returns: "True" If strValue has one '*' or  '?'

 

UseLike = False

If InStr(strValue, "*") Or _

   InStr(strValue, "?") Then

   UseLike  = True

End If

End Function

 

Sub MakeSelect(strValue As Variant)

' Purpose: example using "UseLike" function.

Dim strSelect As String

 

strSelect =  "SELECT * FROM Employees WHERE"

If UseLike(strValue) Then

   strSelect = strSelect & "[LastName] Like '" & _

    strValue &  "'"

Else

   strSelect = strSelect & "[LastName] = '" & _

    strValue &  "'"

End If

 

End Sub