[RESOLVED] Queries using the "LIKE" operator, Access works/VB isn't

[kows]

Alright, I have a program querying a database to create a list of unique company names. It works fine, however, I'm adding a "filter" addition to it and so I'll be using the LIKE operator to make it work, obviously. The code works fine when using a normal query without LIKE, and otherwise returns empty, even if it -should- be able to find the records. When using the same query in Access, I returned 6 records, but in VB nothing seems to work. Can anyone see something I'm doing wrong, or something I could try to see if it would work..?

Dim qry As ADODB.Recordset, query As String
    Set qry = New ADODB.Recordset
 
    query = "SELECT DISTINCT companyname FROM companyrecords WHERE companyname LIKE '*a*'"
    ' this query works fine: "SELECT DISTINCT companyname FROM companyrecords"
 
    qry.Open query, dbConn, adOpenDynamic, adLockReadOnly, adCmdText
    
    If qry.EOF And qry.BOF Then
      MsgBox "no records"
      Exit Sub
    End If
    
    Do Until qry.EOF
      MsgBox qry!CompanyName
      qry.MoveNext
    Loop
    qry.Close
    Set qry = Nothing

I'm clueless at this point, I've never worked with Access and VB before doing this project, and my experience with databases comes mostly from MySQL, but the syntax is nearly exactly the same, which is why I have no idea what's going wrong.

[MartinLiss]

I believe that in VB you need to use the % character rather than the * with Like.

[RhinoBull]

Use "%" as your wild card character.

[RhinoBull]

I believe that in VB you need to use the % character rather than the * with Like.

It's not VB but rather ADO that doesn't recognize the asterisc as a wild card.

[kows]

I believe that in VB you need to use the % character rather than the * with Like.

oh, jeez. I was reading through these forums the other day, and someone said it was * for Access (while % is for MySQL, which is why I was doing it like this). But, anyway.. thanks! it works fine now.. I knew I couldn't have been doing much wrong.

edit: thanks to you too, Rhino. I'll give you a rating as well, just because I'm glad I can finish my project now @_@

[MartinLiss]

It's not VB but rather ADO that doesn't recognize the asterisc as a wild card.

Thanks, I didn't know that.