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!

 

[ArtieChoke]

Change the "or" to "and" and you should get the desired result!

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[petermeachem]

That will only get a result if everything matches won't it. Supposing that the textboxes are blank if you don't want to use them for searching, you could use something like.

if txt1 <> "" and fields("a") like txt1 then
if txt2 <> "" and fields("b") like txt2 then
if txt3 <> "" and fields("c") like txt3 then

etc

endif
endif
endif

Have you got loads of addresses here? If so, you might be better off with a WHERE clause in the recordset rather than looping through the thing.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[petermeachem]

That was rubbish!

It seems that sometimes you want AND and sometimes OR, Tony AND Florida, Tony OR Rose. You need to code differently for each case.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[chiph]

You do need to check for blanks (lack of data entered by the user), and you might need to allow the user to specify some AND/OR criteria (depends on your needs).

When I have a requirement like this, I usually end up looping through the fields, building the SQL as I go, and not trying to create a single large massive string all at once.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[BobRodes]

With myRs
   If (txtMyText1 = "" Or .Fields("myFld1") Like txtMyText1) And _
      (txtMyText2 = "" Or .Fields("myFld2") Like txtMyText2) And _
       'etc
   Then
       'include the record
   End If
End With

HTH

Bob

 

[unborn]

Bob think that was close but it still pulls up everything but now it pulls up EVERYTHING no matter what i do. man this is going to be hidious.. think ill have to make an individual if for each control and if it isnt blank then set it true to search with.. hmm is there a way i can do a loop for this? for just specified text boxes? ok let me go tinker a little. thanks

Running in circles is what I do best!

 

[unborn]

chiph can you go into detail what do you mean building as you go and not make a long string? is sql easier way to go?

Running in circles is what I do best!

 

[unborn]

Petere what you said worked, i was trying to avoid doinf it because i think this really tacky and i feel there has to be more of a compact way to do this but i went ahead and did it wasnt as bad as i thought it would be just alot of c&p hehe but for the code below is there an easier way?

Do Until countx = dbMain.Recordset.RecordCount 'loop all the database data and search the string
If txtBName = "" Or LCase(dbMain.Recordset.Fields("CompanyName")) Like LCase(txtBName) Then
 If LCase(txtFName) = "" Or LCase(dbMain.Recordset.Fields("FirstName")) Like LCase(txtFName) Then
  If LCase(txtLName) = "" Or LCase(dbMain.Recordset.Fields("LastName")) Like LCase(txtLName) Then
   If LCase(txtMInitial) = "" Or LCase(dbMain.Recordset.Fields("MiddleName")) Like LCase(txtMInitial) Then
    If LCase(txtStreet) = "" Or LCase(dbMain.Recordset.Fields("StreetAddress")) Like LCase(txtStreet) Then
     If LCase(txtCity) = "" Or LCase(dbMain.Recordset.Fields("City")) Like LCase(txtCity) Then
      If LCase(txtState) = "" Or LCase(dbMain.Recordset.Fields("StateOrProvince")) Like LCase(txtState) Then
       If LCase(txtZip) = "" Or LCase(dbMain.Recordset.Fields("PostalCode")) Like LCase(txtZip) Then
        If LCase(txtHomeNum) = "" Or LCase(dbMain.Recordset.Fields("HomePhone")) Like LCase(txtHomeNum) Then
         If LCase(txtWorkNum) = "" Or LCase(dbMain.Recordset.Fields("WorkPhone")) Like LCase(txtWorkNum) Then
          If LCase(txtCellNum) = "" Or LCase(dbMain.Recordset.Fields("MobilePhone")) Like LCase(txtCellNum) Then
           If LCase(txtEmail) = "" Or LCase(dbMain.Recordset.Fields("EmailAddress")) Like LCase(txtEmail) Then
            If LCase(txtBdate) = "" Or LCase(dbMain.Recordset.Fields("Birthdate")) Like LCase(txtBdate) Then
             If LCase(txtSAccount) = "" Or LCase(dbMain.Recordset.Fields("CustomerID")) Like LCase(txtSAccount) Then
        'if the recordset is same
        
        
        If arow <= 7 Then
            With flxSearch
                .TextMatrix(arow, 0) = Format(dbMain.Recordset.Fields("CustomerID"), "00000000")
                .TextMatrix(arow, 1) = dbMain.Recordset.Fields("CompanyName")
                .TextMatrix(arow, 2) = dbMain.Recordset.Fields("FirstName")
                .TextMatrix(arow, 3) = dbMain.Recordset.Fields("LastName")
            End With
            arow = arow + 1
            pbSProgress.Value = pbSProgress.Value + 1
        Else
            
            flxSearch.AddItem ""
            With flxSearch
                .TextMatrix(arow, 0) = Format(dbMain.Recordset.Fields("CustomerID"), "00000000")
                .TextMatrix(arow, 1) = dbMain.Recordset.Fields("CompanyName")
                .TextMatrix(arow, 2) = dbMain.Recordset.Fields("FirstName")
                .TextMatrix(arow, 3) = dbMain.Recordset.Fields("LastName")
            End With
            arow = arow + 1
            pbSProgress.Value = pbSProgress.Value + 1
        End If
        
        
                
        
        'flxSearch.AddItem dbMain.Recordset.Fields("CustomerID")
        countx = countx + 1
        dbMain.Recordset.MoveNext
                
    Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
    End If
             End If
                 Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
            End If
                Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
           End If
               Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
          End If
              Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
         End If
             Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
        End If
            Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
       End If
           Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
      End If
          Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
     End If
         Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
    End If
        Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
   End If
       Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
  End If
    Else 'if the recordset is not same then go to the next line
        dbMain.Recordset.MoveNext
        countx = countx + 1
        pbSProgress.Value = pbSProgress.Value + 1
 End If


Loop

man just seems there would be a better way such a waist of space

Running in circles is what I do best!

 

[unborn]

Oh you are talking about sql term for WHERE.. blah ok if that will be the best method back to the drawing board and back to reading hehe its just hard to find what i need everything im finding is confusing.

Running in circles is what I do best!

 

[fredericofonseca]

The solution to your problem will vary depending on whether you need to have the full original recordset available to you at all times or not.

The way you have presented us with your code it seems that you are doing a FULL SELECT of a table (or set of tables), and that you are then looping through each record to see if it matches the values entered on your form.
This is one way of working, and not one I would normally use.


Another way, my preferred, is to build the SQL string based on the values entered on the form, and then execute that SQL,



Something like this.
dim bWhere as Boolean
dim sSql as string


sSql = "select fld1, fld2, fld3 from mytbl "

bWhere = false

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

if txtLName.value <> "" then
if Not bWhere then
sSql = sSql & " where "
else
sSql = sSql & " and "
end if
sSql = sSql & " LastName like '%" & txtLName.value & "%'"
end if

and so on.


The above solution will work but isn't very versatile.
If you wish to have a fully versatile dynamic SQL then look at the way MSQuery from MSOffice deals with criteria just so you have an idea of ways to do things.

One common way of dealing with dynamic SQL is to present a form with 5 columns which can be populated as follows.

And/OR select === Field name ==== Field operation ==== value 1 ==== value 2==

where
"And/Or" is a listbox with options AND or OR
"Field name" is a listbox containing the fields available on the table(s) that are being selected
"Field operation" is a listbox containing the possible operations such as "equal" "not equal" "between" "less than" and so on.
"Value 1" is either the single input field to enter a value, or is the first part of a "between" field operation.
"Value 2" is the second part of a "between" operation.

With something like this you can give a very versatile query tool to your users.

The SQL would be build based on the selections made by the user, and you could easily allow them for And/or combinations which does not seem to be possible with your current form.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[unborn]

Thank you!!

Im actually making a program to hold my database of clients. I want it so i can put in information about the client and beable to pull them up so if i dont remember everything about them. This is also my learning leason so when i get mine done i have another person who needs one for his small business so i want to make it as close to what hell need and i need so i can do a easy conversion though we are in 2 completely diffrent fields.

What you have provided me with is great and i will be playing with this for sure to learn exactly how to get this sql thing up and going but since you did such a wonderful job at displaying how i would build the search string can you perhaps put me in the right direction for adding it to my MSFlex Grid? I am not quite sure how i would use the information once i have it from the database. Thanks again for the wonderful post! Here is the code im using currently, perhaps it would still work? Thank you once again!

        If arow <= 7 Then
            With flxSearch
                .TextMatrix(arow, 0) = Format(dbMain.Recordset.Fields("CustomerID"), "00000000")
                .TextMatrix(arow, 1) = dbMain.Recordset.Fields("CompanyName")
                .TextMatrix(arow, 2) = dbMain.Recordset.Fields("FirstName")
                .TextMatrix(arow, 3) = dbMain.Recordset.Fields("LastName")
            End With
            arow = arow + 1
            pbSProgress.Value = pbSProgress.Value + 1
        Else
            
            flxSearch.AddItem ""
            With flxSearch
                .TextMatrix(arow, 0) = Format(dbMain.Recordset.Fields("CustomerID"), "00000000")
                .TextMatrix(arow, 1) = dbMain.Recordset.Fields("CompanyName")
                .TextMatrix(arow, 2) = dbMain.Recordset.Fields("FirstName")
                .TextMatrix(arow, 3) = dbMain.Recordset.Fields("LastName")
            End With
            arow = arow + 1
            pbSProgress.Value = pbSProgress.Value + 1
        End If

I know if i use a controlless code i would get rid of the control name but would i still access the data the same with with the new controlless recordset?

Thank you once again!! I really appreciate it! (apparently huh? laugh!) Thanks!

Running in circles is what I do best!

 

[BobRodes]

Now, I'm not understanding what you're trying to do, unborn. Would you mind taking 3 text boxes, giving an idea of what inputs should go in each? Let's say last name, first name, and state. I understood you to mean that each text box was independent of each other one, and that the result would be cumulative in terms of where clauses.

e. g.
Tony in first name returns all records with first name Tony.
Rose in last name returns all records with last name Rose.
Rose in last name and CA in state returns all records with last name Rose in CA.

And so on. Is that wrong? Also, why are you using the LIKE operator? That's intended to be used with wild cards. Are users putting wild cards in text boxes?

Bob

 

[fredericofonseca]

Avoid using MSFlextGrid as it is pretty limited. Use instead the MSHFlexGrid.

Just a small sample of a way of loading a MSHFlexGrid, also with a some SQL with a variable select.

Private Sub load_process_Click()
Dim s As String
Dim lrows As Long

sSql = "Select * from processes where process_id = '" & proc_id.Text & "'"
sSql = sSql + " order by process_id, process_seq"
Set rs = New ADODB.Recordset
rs.Open sSql, adoConn, adOpenForwardOnly, adLockReadOnly
flex1.Redraw = False
flex1.Clear
s = "<Type |<Description |<Contents"
flex1.FormatString = s
flex1.ColWidth(0) = 1500
flex1.ColWidth(1) = 2000
flex1.ColWidth(2) = 3000
flex1.ColWidth(3) = 0
flex1.ColWidth(4) = 0

s = ""
lRow = 1
While Not rs.EOF And Not rs.BOF
  lrows = lRow + 1
  flex1.Rows = lrows
  flex1.Row = lRow
  flex1.Col = 0
  flex1.Text = GetString(rs.Fields("type").Value)
  flex1.Col = flex1.Col + 1
  flex1.Text = GetString(rs.Fields("description").Value)
  flex1.Col = flex1.Col + 2
  flex1.Text = GetString(rs.Fields("logintext").Value)
  flex1.Col = flex1.Col + 1
  flex1.Text = GetString(rs.Fields("loginid").Value)
  flex1.Col = 2
  flex1.Text = GetString(rs.Fields("contents").GetChunk(20000))
  lRow = lRow + 1
  rs.MoveNext
Wend
If lrows < MinListRows Then
  flex1.Rows = MinListRows
End If
flex1.Redraw = True
End Sub

Public Function GetString(vData As Variant) As Variant
If IsNull(vData) Or vData = Space(255) Then
  GetString = ""
Else
  GetString = vData
End If

End Function

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[unborn]

Bob,

e. g.
Tony in first name returns all records with first name Tony.
Rose in last name returns all records with last name Rose.
Rose in last name and CA in state returns all records with last name Rose in CA.
Tony in first name, Rose in last name, and FL in state would return all the Tony Rose in FL.

Is 100% correct. I have my form with ALL the field in my database searchable except for maybe 3 which would be for internal application use. So i can search for the business name or the zip code or account number. I have uploaded 2 picks of my program one before search and one after.

Before:

http://www.mindchips.com/images/dbsearchss.JPG

After:

http://www.mindchips.com/images/dbsearchssdone.JPG

Running in circles is what I do best!

 

[BobRodes]

Very good, thank you. Now, why are you using the LIKE operator exactly?

Bob

 

[unborn]

Thanks for the help fred i hightly appreciate it now off to the whiteboard to see what i can do with the new found knowledge!! Thanks again so much!

Running in circles is what I do best!

 

[unborn]

Oh ya the Like thing was something i picked up from another example? I really didnt know why i was using it i just saw it in the example and well used it and it was working laugh :/ I was going to use = but i figured i didnt want to break it and like was working.

I wouldnt mind wild key sort of like..

If i typed in Ton* it would find Tony, Tones, Tons of stuff. Thats probably somehtign i can do with the sql string im going to have to find a good resource on exactly how to handle the strings.

I work with php to and i cant seem to nail the mySQL thing either the databases are a little confusing for me i can get a little of them hopefully this will help me out.

Running in circles is what I do best!

 

[BobRodes]

Well, there's a MySQL forum you can ask those kinds of questions in. Um, I don't see why my logic doesn't work. Maybe I'm being dumb. I'll go through it again and check.

Bob

 

[BobRodes]

Ok, I've looked at mine a little while, and I still can't see what I'm doing wrong. In any case, Frederico's model is what I would do. If you must use a data control, you could use Frederico's model to set the Recordset.Filter property.

Bob