Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

Using the * operator in sql query

I want to compare some elements in fields to user inputted data but I don't want a comparison restricted to a perfect match.
For example in mySQL it would be
              SELECT test From TEST1 Where test = 'a%'
to get all the elements in test that start with 'a'.
In access it uses the '*' operator but when I use the same code in vb 6 it returns an empty recordset even if The data is definately there. The where part of the sql syntax is constructed in the following function, 'sqlResult' is a string array got from splitting a string, it contains a list of the fields to be searched.

Public Sub whereLoad(ByVal sqlResult As Variant, ByVal index As Integer)
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    For j% = 0 To UBound(sqlResult)
        If j > 0 Then
            g_sqlWhere(index) = g_sqlWhere(index) & " OR "
        End If
        For k% = 0 To UBound(g_searchFinal)
            If k > 0 Then
                g_sqlWhere(index) = g_sqlWhere(index) & " OR "
            End If
            g_sqlWhere(index) = g_sqlWhere(index) & sqlResult(j) & " = " & "'*" & g_searchFinal(k) & "*' "
        Next k%
    Next j%
End Sub

If I remove the '*''s and search for a record I know is there it returns it but if I put in the *'s it returns an empty record?
1 Solution
g_sqlWhere(index) = g_sqlWhere(index) & "instr(" & sqlResult(j) & ", '" &  g_searchFinal(k) & "') >0"

with the intened result that your sql string is of the form

select fieldx from tabley where instr(fieldx,'search text') > 0
that should be:

SELECT test From TEST1 Where test LIKE 'a%'

the = operator tests ONLY for EXACT equality, so you would only find a record if the field called TEST contained EXACTLY a%.

The LIKE operator would return all records where the Field TEST STARTED with the single letter 'a', followed by ANY characters.

The % is the correct 'wildcard' top use, when using ADO as the Data Access tecnology.  If you are using DAO (Microsoft Access JET Engine, for example), you would use the * character as the 'wildcard:

SELECT test From TEST1 Where test LIKE 'a*'  (DAO Only)

same would apply to this line:

 g_sqlWhere(index) = g_sqlWhere(index) & sqlResult(j) & " = " & "'*" & g_searchFinal(k) & "*' "

DO NOT use the = sign, rather use LIKE:

 g_sqlWhere(index) = g_sqlWhere(index) & sqlResult(j) & " LIKE " & "'*" & g_searchFinal(k) & "*' "


Be interested to know how VB is connecting to the Access, i.e. dao, ado, adodc etc.

Been a while since I had to connect vb to access (opposed to sql svr) but I have this vague niggle in the back of my memory that is telling my ADO uses the % sign for wildcard even when connecting to access.

Another quick test is use the 'like' not equals within the comparison, i.e. fieldname like '%a%'

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

itzmeAuthor Commented:
Okay thats got it going only problem is that returns only one record?
Thanks anyways guys
how are you determining that there is only one record?  I use this EXACT syntax all the time, and always get the correct number of matching records.  This suggestes that you may have some other issues, beyond what you have described here.

itzmeAuthor Commented:
Sorry made a mistake its working fine just a silly mistake with the EOF and BOF trappers.
It's all working fine

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now