Member Avatar for joel.hahn

I am having issues with the syntax for a like statement in VBnet. I am using access as a back end. here is the code. Any help would be appreciated.

    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim strsql As String

    strsql = "SELECT Count(TractStatus.txOwnerID) AS CountOftxOwnerID " & _
                "FROM Owner INNER JOIN TractStatus ON Owner.txOwnerID = TractStatus.txOwnerID " & _
                "GROUP BY TractStatus.txTrid, Owner.txLastName " & _
                "HAVING (((TractStatus.txTrid)= '" & cbTractSearch.Text & "') AND ((Owner.txLastName) Like "USA*" Or (Owner.txLastName) Like "state*"))"

    cn = New ADODB.Connection
    cn.Open(strConnect)

    'Set rs = New ADODB.Recordset
    rs = cn.Execute(strsql)

    If rs.EOF Then
        txGov.Text = 0
    Else
        If rs.Fields(0).Value & vbNullString <> "" Then
            txGov.Text = "GOV"
        Else
            txGov.Text = ""
        End If

    End If
    rs.Close()
    cn.Close()
  • 1 Contributor
  • 1 Reply
  • 135 Views
  • 12 Minutes Discussion Span
  • Latest Post Latest Post by joel.hahn
Member Avatar for joel.hahn

found it,

   Dim rs As ADODB.Recordset
        Dim cn As ADODB.Connection
        Dim strsql As String
        Dim stval1 As String
        Dim stval2 As String

        stval1 = "usa"
        stval2 = "state"
        strsql = "SELECT Count(TractStatus.txOwnerID) AS CountOftxOwnerID " & _
                    "FROM Owner INNER JOIN TractStatus ON Owner.txOwnerID = TractStatus.txOwnerID " & _
                    "GROUP BY TractStatus.txTrid, Owner.txLastName " & _
                    "HAVING (((TractStatus.txTrid)= '" & cbTractSearch.Text & "') AND ((Owner.txLastName) Like '%" & stval1 & "%' Or (Owner.txLastName) Like '%" & stval2 & "%'))"

        cn = New ADODB.Connection
        cn.Open(strConnect)

        'Set rs = New ADODB.Recordset
        rs = cn.Execute(strsql)

        If rs.EOF Then
            txGov.Text = 0
        Else
            If rs.Fields(0).Value & vbNullString <> "" Then
                txGov.Text = "GOV"
            Else
                txGov.Text = ""
            End If

        End If
        rs.Close()
        cn.Close()
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.