Results 1 to 8 of 8

Thread: [RESOLVED] VB6 using Like Operator in VB6 and Access

  1. #1

    Thread Starter
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Resolved [RESOLVED] VB6 using Like Operator in VB6 and Access

    Never liked the LIKE operator, so steered away from this easy way of searching, but now it is the best way for my current project.

    What AM I doing wrong with this visual basic 6.0 statement?
    Code:
    ssql = "select Firstname from myTable where trim(LASTNAME) like 'B*'"
    I obviously want to find all lastnames that start with the letter "B", but even though I have "BROWN" as a last name (for example), that query does not find it.

    I know it has to be simple, but can't get this one to work. Access uses the *, and SQLPLUS uses the %--have tried most things, and the obvious is not obvious to me. Sure could use a boost here.
    Thanks

  2. #2
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    Re: VB6 using Like Operator in VB6 and Access

    Select Lastname instead of Select Firstname ?

  3. #3

    Thread Starter
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: VB6 using Like Operator in VB6 and Access

    no...same thing...those are not really the fields I'm using in my project, just an example.

    I'll put together a small program that mimics this behavior...maybe that will help....

  4. #4

    Thread Starter
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: VB6 using Like Operator in VB6 and Access

    Here's an example program that shows I am somehow improperly using the LIKE operator in my query.

    Attachment 129609

    code is below, but program has sample db and vb project.

    Code:
    Option Explicit
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Private Sub Command1_Click()
        List1.Clear
        Set rs = New ADODB.Recordset
        Dim ssql As String
        ssql = "select LASTNAME, FIRSTNAME from EMPS"
        Set rs = cnn.Execute(ssql)
        Do While Not rs.EOF
            List1.AddItem (rs!lastname & ", " & rs!firstname)
            rs.MoveNext
        Loop
    End Sub
    
    Private Sub Command2_Click()
        List1.Clear
        Set rs = New ADODB.Recordset
        Dim ssql As String
        ssql = "select LASTNAME, FIRSTNAME from EMPS where LASTNAME like 'B*'"
        Set rs = cnn.Execute(ssql)
        Do While Not rs.EOF
            List1.AddItem (rs!lastname & ", " & rs!firstname)
            rs.MoveNext
        Loop
    End Sub
    
    Private Sub Form_Load()
        Dim ConnectionString As String
        Set cnn = New ADODB.Connection
        Set rs = New ADODB.Recordset
        cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
        cnn.ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & App.Path & "\db1.accdb;"
        cnn.Open ConnectionString
        rs.CursorLocation = adUseClient
        rs.Open "emps", cnn, adOpenKeyset, adLockPessimistic, adCmdTable
    End Sub

  5. #5

    Thread Starter
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: VB6 using Like Operator in VB6 and Access

    NEVER MIND....got it.....DO use the % sign, NOT the *...ADO thing...jeez...wasted three hours!

    thanks for looking....

    Sammi

  6. #6
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: [RESOLVED] VB6 using Like Operator in VB6 and Access

    If you read the documentation that comes with VB6 there is a discussion of Jet SQL syntax that covers this operator.

    DAO uses an older SQL syntax similar to the SQL-89 standard. In this version of the language LIKE patterns are formed similarly to VB's LIKE patterns, probably because SQL-89 didn't provide a definition.

    The Jet 4.0 OLEDB Provider (normal choice when using ADO) or the later ACE OLEDB Providers are used a newer syntax similar to SQL-92 is used. By then LIKE patterns were defined, and for whatever reason "%" was chosen instead of "*" and "_" instead of "?" so you should make those substitutions.

    See your October 2001 MSDN Library CDs, topic: "Fundamental Microsoft Jet SQL for Access 2000."
    Last edited by dilettante; Aug 25th, 2015 at 12:43 PM. Reason: oops, too slow!

  7. #7

    Thread Starter
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: [RESOLVED] VB6 using Like Operator in VB6 and Access

    Thx dile.....shoulda/coulda done that. I DID search forums however, and not ONCE could I find that ADO (er, Jet 4.0 and ACE providers, excuse me) uses these 'NEW' LIKE patterns.

  8. #8
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: [RESOLVED] VB6 using Like Operator in VB6 and Access

    Keep in mind that you can use ADO through the thunking layer (MSDASQL Provider) to reach an ODBC Driver. While sloppy and wasteful it works, but you must use the old-style SQL syntax then because every Jet ODBC Driver I ever saw uses the DAO SQL syntax.

    ADO itself knows nothing about SQL and doesn't look at it at all but merely passes it through.

    OLE DB Provider for ODBC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width