help with sql statement

hi all
I have been runing into a wall trying to get this statement to work
I want to be able to search DB by either Address or by the customers name.
The address search works due to help from dee-u and I can get the search for a name to work except I can't pass the SQL statement the customers whole name ie: John Smith. It will work if I change sql to LastName or FirstName . then by entering either there first or last name the search will work.
I know it's just that I don't have the sql statement right. to search both FirstName and LastName fields but I can't seem to build a correct statement.
any help
bty Thanks dee-u for the help allready

VB Code:

Private Sub cmdSearch_Click()
Dim Address As String
Dim LastName As String
Dim FirstName As String
 
    If Len(Trim$(txtSearchBox)) = 0 Then
        MsgBox "Please input a value"
        txtSeachbox.SetFocus
        Exit Sub
    End If
 
    If Option1 = True Then
        Address = Trim$(txtSearchBox)
        frmCustomers.datPrimaryRS.Recordset.MoveFirst
        frmCustomers.datPrimaryRS.Recordset.Find "Address = '" & Address & "'"
    End If
    If Option2 = True Then
        FirstName = Trim$(txtSearchBox)
        LastName = Trim$(txtSearchBox)
        frmCustomers.datPrimaryRS.Recordset.MoveFirst
        frmCustomers.datPrimaryRS.Recordset.Find "FirstName = '" & FirstName & "'" And "LastName = '" & LastName & "'"
    End If
   
Unload Me
End Sub

The Find property only allows you to use one criteria, instead try using the Filter property instead:

VB Code:

rs.Filter "FirstName = '" & FirstName & "'" And "LastName = '" & _  
LastName & "'"

See this post

ok I agree

but this

VB Code:

frmCustomers.datPrimaryRS.Recordset.Filter "FirstName = '" & FirstName & "'" And "LastName = '" & _
LastName & "'"

creates a invalid use of property error

Quote:

---

Originally Posted by crater

ok I agree

but this

VB Code:

frmCustomers.datPrimaryRS.Recordset.Filter "FirstName = '" & FirstName & "'" And "LastName = '" & _
LastName & "'"

creates a invalid use of property error

---

Try this instead:

VB Code:

[B]datPrimaryRS.Filter[/B] "FirstName = '" & FirstName & "'" And "LastName = '" & _
LastName & "'"

now it's Type Mismatch

VB Code:

Private Sub cmdSearch_Click()
Dim Address As String
Dim LastName As String
Dim FirstName As String
 
    If Len(Trim$(txtSearchBox)) = 0 Then
        MsgBox "Please input a value"
        txtSeachbox.SetFocus
        Exit Sub
    End If
 
    If Option1 = True Then
        Address = Trim$(txtSearchBox)
        frmCustomers.datPrimaryRS.Recordset.MoveFirst
        frmCustomers.datPrimaryRS.Recordset.Find "Address = '" & Address & "'"
    End If
    If Option2 = True Then
        FirstName = Trim$(txtSearchBox)
        LastName = Trim$(txtSearchBox)
        frmCustomers.datPrimaryRS.Recordset.MoveFirst
        datPrimaryRS.Recordset.Filter ("FirstName = '") & FirstName & "'" And ("LastName = '") & _
LastName & "'"
    End If
   
Unload Me
End Sub

and I think I have to have the frmCustomer. beause this is a different form not a searchbox
but when I add it with that code I get Invalid use of Property error take it away and type mismatch error