Using the * operator in sql query

Posted on 2003-03-13
Medium Priority
Last Modified: 2006-11-17
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?
Question by:itzme
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 18

Expert Comment

ID: 8128865
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
LVL 44

Accepted Solution

Arthur_Wood earned 200 total points
ID: 8128874
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) & "*' "



Expert Comment

ID: 8128907
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%'

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 8128991
Okay thats got it going only problem is that returns only one record?
Thanks anyways guys
LVL 44

Expert Comment

ID: 8129571
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.


Author Comment

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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question