Vb Form And Ms Access Sql Wildcard Search

My table table1 has 3 fields: FName, LName, Phone. I am using Microsoft Access 2010 for running the SQL query. Some rows has empty / null phone values. I have a VB form which accep

Solution 1:

I think it should be easier to sort this one out if we simplify it. So I arbitrarily decided to ignore LName for now, and do the searching based only on FName or Phone.

In my version of frmSearchMain, I named the text box which holds the search target value for FName as txtSearchFName simply because I prefer to name the control differently than the record source field. Similarly, I chose txtSearchPhone for the Phone search target text box.

Since I already had a table named Table1, I called mine tblKeerthiram instead.

With those changes, this query gives me rows which match txtSearchFName or txtSearchPhone. If both txtSearchFName and txtSearchPhone are Null, the query return all rows from the table ... which is what I hope you want.

SELECT
    t1.id,
    t1.FName,
    t1.PhoneFROM tblKeerthiram AS t1
WHERE
           (t1.FName Like "*"
               & [Forms]![frmSearchMain]![txtSearchFName]
               & "*"
        OR [Forms]![frmSearchMain]![txtSearchFName] Is Null)
    AND
           (t1.Phone Like "*"
               & [Forms]![frmSearchMain]![txtSearchPhone]
               & "*"
        OR [Forms]![frmSearchMain]![txtSearchPhone] Is Null)
ORDER BY t1.FName;

A consequence of this approach is that, if both txtSearchFName and txtSearchPhone are non-Null, the query will return only the rows which match both. As I understood your description, that is not what you want. You want to search by only one or the other, not both at the same time.

In that case I suggest you use the after update events for the two search text boxes to set the other Null when a non-Null value has been entered into one of them. I'm unsure how clear that sentence was, so just add this code to your form's module. It will ensure that only one of the two text boxes will contain a non-Null value.

OptionCompare Database
OptionExplicitPrivateSub txtSearchFName_AfterUpdate()
    IfNot IsNull(Me.txtSearchFName) ThenMe.txtSearchPhone = Null
    EndIfEndSubPrivateSub txtSearchPhone_AfterUpdate()
    IfNot IsNull(Me.txtSearchPhone) ThenMe.txtSearchFName = Null
    EndIfEndSub

Finally if that all works, then you only need to revise it to deal with LName. Hope that part won't be too daunting. Good luck.

Solution 2:

In your example this query give the result for a record that match all the 3 conditions:

SELECT table1.LName, table1.FName, table1.Phone FROM table1 WHERE table1.Phone Like ('*'& Forms!frmSearchMain!Phone &'*') AND table1.LName Like ('*'& Forms!frmSearchMain!LName &'*') AND table1.FName Like ('*'& Forms!frmSearchMain!FName &'*') ORDERBY table1.LName, table1.FName;

If you want return the records that match (Phone) OR (FName AND LName) you have to do this:

SELECT table1.LName, table1.FName, table1.Phone FROM table1
WHERE  (table1.Phone Like ('*'& Forms!frmSearchMain!Phone &'*'))
OR (table1.LName Like ('*'& Forms!frmSearchMain!LName &'*') AND table1.FName Like ('*'& Forms!frmSearchMain!FName &'*'))
ORDERBY table1.LName, table1.FName;

Hope it help

Solution 3:

sql = "SELECT LName, FName, Phone" & _
      "FROM table1" & _
      "WHERE (" & _
      "Phone LIKE ('%" & Forms!frmSearchMain!Phone & "%' )" & _
      "OR ( FName LIKE ('%" & Forms!frmSearchMain!FName & "%' )" & _
            "AND LName LIKE ('%" & Forms!frmSearchMain!LName & "%' ) )" & _
      "ORDER BY LName, FName"

Running that query should return all LName, FName, Phone which have the correct first AND last name, or the correct phone (or both).

Note: paraphrase that, I honestly don't know how to properly concatenate those multi-line VBA strings.