Better method of searching DB? 4

[unborn]

This code is a snippet of the code im using. The problem is and i know why is if i put tony(txt1) in it will pull up everything tony, if i put tony(txt1) rose(txt2) in it will put everything with tony(txt1) and rose(txt2) intead of strictly only the tony roses. If i put like Tony(txt1) FL(txt3) it will pull up everything in florida and everything tony. these are all different text boxes as you see below. Now i want if i put Tony(txt1) FL(txt3) it will only pull up the tonys in florida not everybody named tony and everybody in florida. Is there an easier method of doing this then.. well i actually am not sure on where to start. some suggestions please? Thanks!

Do Until countx = dbMain.Recordset.RecordCount 'loop all the database data and search the string
    If LCase(dbMain.Recordset.Fields("CompanyName")) Like LCase(txtBName) Or _
        LCase(dbMain.Recordset.Fields("FirstName")) Like LCase(txtFName) Or _
        LCase(dbMain.Recordset.Fields("LastName")) Like LCase(txtLName) Or _
        LCase(dbMain.Recordset.Fields("MiddleName")) Like LCase(txtMInitial) Or _
        LCase(dbMain.Recordset.Fields("StreetAddress")) Like LCase(txtStreet) Or _
        LCase(dbMain.Recordset.Fields("City")) Like LCase(txtCity) Or _
        LCase(dbMain.Recordset.Fields("StateOrProvince")) Like LCase(txtState) Or _
        LCase(dbMain.Recordset.Fields("PostalCode")) Like LCase(txtZip) Or _
        LCase(dbMain.Recordset.Fields("HomePhone")) Like LCase(txtHomeNum) Or _
        LCase(dbMain.Recordset.Fields("WorkPhone")) Like LCase(txtWorkNum) Or _
        LCase(dbMain.Recordset.Fields("MobilePhone")) Like LCase(txtCellNum) Or _
        LCase(dbMain.Recordset.Fields("EmailAddress")) Like LCase(txtEmail) Or _
        LCase(dbMain.Recordset.Fields("Birthdate")) Like LCase(txtBdate) Or _
        LCase(dbMain.Recordset.Fields("CustomerID")) Like LCase(txtSAccount) Then
        'if the recordset is same

Thanks for the help!

Running in circles is what I do best!

 

[unborn]

Ya yours looked like it was going to work for me but then it just did like what i had already hehe. I heard doing it controlless is the way to go i just am unsure of how to do this. Well now im finding out and i am reviewing the SQL tutorial on W3 helping me build my search query.

Since i am grabbing just the ID, Business, First, and Last "for now" i would build it like.

sSql = "SELECT CustomerID, CompanyName, FirstName, LastName FROM Clients "

and then with the field checkers that build the string for me will then check all fields filled in for matching even if it is not in the 4 defined fields above im assuming still building the code for it. Ill post back in few with what ive come up with.

Running in circles is what I do best!

 

[unborn]

If txtFName.Value <> "" Then
If Not bWhere Then
sSql = sSql & " WHERE "
Else
sSql = sSql & " AND "
End If
sSql = sSql & " FirstName LIKE '%" & txtFName.Value & "%'"
End If

Im assuming bWhere is the Or/And checker? Let me read this correctly out loud to see if i understand.

If FN is not blank then if checking OR is true use Where statement Else use And then it builds a wild card statement for Tony? so it will find Antony, or Tonya? Thats what the %'s are right?


Running in circles is what I do best!

 

[BobRodes]

You can do a couple of things to find out if a where clause is necessary (which it is if any of the text boxes aren't blank):

dim cCtl as Control
Dim isWhere as Boolean
For each cCtl in Controls
   if TypeOf cCtl is TextBox then
      isWhere = True
   End If
Next

This only works as written if you're evaluating all the text boxes on the form. If you have to exclude some, you have to check cCtl.Name, and that can get tedious very quickly if you have to do it too often.
The other things you can do are
1. Put the text boxes in question in a control array and evaluate the array.
2. Create an array of type TextBox, and set its values equal to the Text boxes that you're using.
3. Check in line, using Fred's bWhere idea.

I'd say it's about a tossup as to whether Fred's method or this one is more efficient.

Bob

 

[unborn]

This is the string that was generated when i put int he FN LN and ST is this a valid search query?

SELECT CustomerID, CompanyName, FirstName, LastName FROM Clients  WHERE  FirstName LIKE '%Tony%' WHERE  LastName LIKE '%Rose%' WHERE  StateOrProvince LIKE '%FL%'

Running in circles is what I do best!

 

[unborn]

Bob thanks for the useful information about going through the controls ya i was hoping in my original post there was a way to go through them even through they are named but i have many more texts on the other tabe then just that section. Wish there was a way to catagorize them.

Running in circles is what I do best!

 

[johnwm]

No - you only need ONE Where:

SELECT CustomerID, CompanyName, FirstName, LastName FROM Clients  WHERE  FirstName LIKE '%Tony%' AND  LastName LIKE '%Rose%' AND  StateOrProvince LIKE '%FL%'

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[unborn]

Thanks john! Through me a curve ball and now i understand the bWhere as where i thought it was a Or/And determiner. So my blocks of code will actually look like..

If txtSAccount <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " CustomerID LIKE '%" & txtSAccount & "%'"
End If

which then created the string..

SELECT CustomerID, CompanyName, FirstName, LastName FROM Clients  WHERE  FirstName LIKE '%Tony%' AND  LastName LIKE '%Rose%' AND  StateOrProvince LIKE '%FL%'

Running in circles is what I do best!

 

[unborn]

Threw* :/

Running in circles is what I do best!

 

[unborn]

Ok i have finished the code but i must be doing somehting wrong. It isnt working Its pulling up everything again.

Search Query

SELECT CustomerID, CompanyName, FirstName, LastName FROM Clients  WHERE  FirstName LIKE '%Tony%' AND  LastName LIKE '%Rose%'

Source Code

flxSearch.Clear
With flxSearch
    .TextMatrix(0, 0) = "Account"
    .ColWidth(0) = 860
    .TextMatrix(0, 1) = "Business"
    .ColWidth(1) = 2240
    .TextMatrix(0, 2) = "First"
    .ColWidth(2) = 1100
    .TextMatrix(0, 3) = "Last"
    .ColWidth(3) = 1100
End With

Dim mCn As ADODB.Connection
Dim mRs As ADODB.Recordset
Dim bWhere As Boolean
Dim sSql As String

Set mCn = New ADODB.Connection
Set mRs = New ADODB.Recordset
mCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\my work\Programs\clients.mdb"
mRs.Open "Clients", mCn, adOpenKeyset, adLockOptimistic, adCmdTable


sSql = "SELECT CustomerID, CompanyName, FirstName, LastName FROM Clients "

bWhere = False

If txtSAccount <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " CustomerID LIKE '%" & txtSAccount & "%'"
End If

If txtBName <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " CompanyName LIKE '%" & txtBName & "%'"
End If

If txtFName <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " FirstName LIKE '%" & txtFName & "%'"
End If

If txtLName <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " LastName LIKE '%" & txtLName & "%'"
End If

If txtMInitial <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " MiddleName LIKE '%" & txtMInitial & "%'"
End If

If txtStreet <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " StreetAddress LIKE '%" & txtStreet & "%'"
End If

If txtCity <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " City LIKE '%" & txtCity & "%'"
End If

If txtState <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " StateOrProvince LIKE '%" & txtState & "%'"
End If

If txtZip <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " PostalCode LIKE '%" & txtZip & "%'"
End If

If txtHomeNum <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " HomePhone LIKE '%" & txtHomeNum & "%'"
End If

If txtWorkNum <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " WorkPhone LIKE '%" & txtWorkNum & "%'"
End If

If txtCellNum <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " MobilePhone LIKE '%" & cellnum & "%'"
End If

If txtBdate <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " BirthDate LIKE '%" & txtBdate & "%'"
End If

If txtEmail <> "" Then
    If Not bWhere Then
        sSql = sSql & " WHERE "
        bWhere = True
    Else
       sSql = sSql & " AND "
   End If
   sSql = sSql & " EmailAddress LIKE '%" & txtEmail & "%'"
End If

txtSqlString = sSql

Dim lrows As Long
flxSearch.Redraw = False


lRow = 1
While Not mRs.EOF And Not mRs.BOF
  lrows = lRow + 1
  flxSearch.Rows = lrows
  flxSearch.Row = lRow
  flxSearch.Col = 0
  flxSearch.Text = GetString(Format(mRs.Fields("CustomerID").Value, "00000000"))
  flxSearch.Col = 1
  flxSearch.Text = GetString(mRs.Fields("CompanyName").Value)
  flxSearch.Col = 2
  flxSearch.Text = GetString(mRs.Fields("FirstName").Value)
  flxSearch.Col = 3
  flxSearch.Text = GetString(mRs.Fields("LastName").Value)
  lRow = lRow + 1
  mRs.MoveNext
Wend

If lrows < MinListRows Then
  flxSearch.Rows = MinListRows
End If
flxSearch.Redraw = True

mRs.Close
mCn.Close

I can almost smell the victory!


Running in circles is what I do best!

 

[unborn]

Score i got it!! Thanks!!

Set Rs = New ADODB.Recordset
Rs.Open [b]sSql[/b], mCn, adOpenForwardOnly, adLockReadOnly

flxSearch.Redraw = False


lRow = 1
While Not Rs.EOF And Not Rs.BOF
  lrows = lRow + 1
  flxSearch.Rows = lrows
  flxSearch.Row = lRow
  flxSearch.Col = 0
  flxSearch.Text = GetString(Format(Rs.Fields("CustomerID").Value, "00000000"))
  flxSearch.Col = 1
  flxSearch.Text = GetString(Rs.Fields("CompanyName").Value)
  flxSearch.Col = 2
  flxSearch.Text = GetString(Rs.Fields("FirstName").Value)
  flxSearch.Col = 3
  flxSearch.Text = GetString(Rs.Fields("LastName").Value)
  lRow = lRow + 1
  Rs.MoveNext
Wend

I didnt have it attached to the results!! thanks again!!

Running in circles is what I do best!

 

[johnwm]

You appear to be fetching the whole table:

mRs.Open "Clients", mCn, adOpenKeyset, adLockOptimistic, adCmdTable

Then constructing a wonderful query string after you've fetched the recordset???

and finally you don't appear to actually use the querystring at all??

I was sort of expecting you to generate that wonderful [tt]sSQL[/tt] and then use it to fetch a new recordset at the endof your code:
mRs.Open sSQL, mCn, adOpenKeyset, adLockOptimistic, adCmdText


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[strongm]

Try putting the correct field names into the .tag property of the textbox you want to match against. The following example requires a form with 4 (empty) textboxes, a command button and the hierarchical flexgrid, and works against the Northwind demo database:

[blue]Option Explicit

Private Sub Command1_Click()
    Dim rs As Recordset
    Set rs = MSHFlexGrid1.DataSource
    rs.Filter = BuildWhereClause
End Sub

Private Sub Form_Load()
    Dim rs As Recordset
    Set rs = GetDisconnectedRecordset
    Set MSHFlexGrid1.DataSource = rs
    
    Text1.Tag = "TitleOfCourtesy"
    Text2.Tag = "FirstName"
    Text3.Tag = "LastName"
    Text4.Tag = "Region"
End Sub

Private Function GetDisconnectedRecordset() As Recordset
    Dim cn As Connection
    Dim rs As Recordset
    Dim strConnect As String
    Dim strSQL As String
    
    ' Your connection string and SQL statement go here ...
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;Persist Security Info=False"
    strSQL = "SELECT TitleOfCourtesy, FirstName, LastName, Region FROM Employees"
    
    Set cn = New Connection
    cn.Open strConnect
    Set rs = New Recordset
    rs.CursorLocation = adUseClient
    rs.Open strSQL, cn, adOpenKeyset
    Set rs.ActiveConnection = Nothing ' disconnect recordset. Handle in memory cursor only
    Set GetDisconnectedRecordset = rs
End Function

Private Function BuildWhereClause() As String
    Dim myText As Control
    Dim strWhere() As String
    Dim myCount As Long
    
    On Error Resume Next
    For Each myText In Controls
        If TypeName(myText) = "TextBox" Then
            If myText.Text <> "" Then
                    ReDim Preserve strWhere(myCount)
                    strWhere(myCount) = myText.Tag & " = '" & myText.Text & "'"
                    myCount = myCount + 1
            End If
        End If
        Err.Clear
    Next
    On Error GoTo 0
    BuildWhereClause = Join(strWhere, " AND ")

End Function[/blue]

 

[BobRodes]

That BuildWhereClause is very nicely done, strongm. You have a gift for doing with a few lines of code what most people require a lot more to do.

One small point:

    rs.CursorLocation = adUseClient
    rs.Open strSQL, cn, adOpenKeyset

Client side cursors are by definition static, so the adOpenKeyset value will be ignored.

Bob

 

[strongm]

Quite right. I changed my mind about where the cursor was going to be because I decided I wanted to disconnect the recordset (with the idea that this might also be of interest to spangeman after thread222-1117230) about half way through putting the example together and missed changing the cursor type (not that it actually causes a problem, since ADO changes it for us) in the resulting code revisions; well caught.

 

[BobRodes]

I had the feeling it was something like that.