Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    58

    Unanswered: Multiple Search Fields

    I have a search form that has 3 text boxes. If a users type data in 2 boxes I want it to search the database for those two search fields. I want the user to be able to search 1,2, or 3 fields.

    I have the following code. I think I need a AND instead of OR.
    Code:
    Private Sub cmdSearch_Click()
        
        Dim strSQLSearch As String
    
        strSQLSearch = "SELECT Inv_ID, StockNbr, Year, Make, Model, Color, UnitStatus FROM tblInventory WHERE make = '" & Me.txtMake & "' or model = '" & Me.txtModel & "' OR color = '" & Me.txtColor & "'"
        Me.lstInventory.RowSource = strSQLSearch
        Me.lstInventory.Requery
    
    End Sub
    Here is the other problem. If the listbox brings up 3 records, no matter which record I click on it brings up the first record.

    Code:
    Private Sub lstInventory_DblClick(Cancel As Integer)
        
        DoCmd.OpenForm "frmVehicle", acNormal, , "Inv_ID = " & Me.lstInventory.Column(0)
        DoCmd.Close acForm, "frmInventorySearch"
        
    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    the AND vs. OR question depends on whether you want to return records that match EITHER string, or BOTH strings.

    Naturally if you want either, the OR is fine, if you want them to both match, then you need to use AND.

    For retrieving the record from the listbox, try using Me.lstInventory.Column(0, lstInventory.listIndex + 1)

  3. #3
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    58
    I want it to be and but if the textbox is null then I don't want to include it in the search string. For example, the fields are make, model, and color. If I type Honda for make and Blue for color I want to populate the listbox with Blue Hondas.

    Code:
    Me.lstInventory.Column(0, lstInventory.listIndex + 1)
    It did not work.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Code:
    Me.lstInventory.Column(0, lstInventory.listIndex + 1)
    It did not work.
    Very odd, that should work just dandy. I use it quite regularly to retrive values from a listbox...

    eg, this is a cut and paste out of one of my subs:

    s = lbPrefs.Column(i, lbPrefs.ListIndex + 1)

    Have you tryed using a msgbox to see what value it's returning?

    As far as the evaluating criteria situation, I would suggest using nz() and setting the criteria to "*" if null, thereby effectively negating the criteria:

    ...
    WHERE make = '" & nz(Me.txtMake, "*") & "' AND model = '" & nz(Me.txtModel, "*") & "' AND color = '" & nz(Me.txtColor, "*") & "'"
    Last edited by Teddy; 04-09-04 at 12:18.

Posting Permissions

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