0

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
2
Views
5 Years
Discussion Span
Last Post by joel.hahn
0

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()
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.