Lookup Search

SinKoh

Registered User.
Local time
Today, 16:11
Joined
Apr 4, 2015
Messages
14
I have a form with a Lookup method. I have it set as this:

Code:
           SearchStr = FilterSearch & "(tblCalls.CustFirstName LIKE " & "'" & txtSearchBox.Value & "*' OR tblCalls.CustLastName LIKE '" & txtSearchBox.Value & "*')"

This will allow the user to search for a record by first or last name. But if they try and search with both it will not return anything. Can someone help me out? I need to to be searchable by first, last, or both.

Thanks.
 
I have a form with a Lookup method. I have it set as this:

Code:
           SearchStr = FilterSearch & "(tblCalls.CustFirstName LIKE " & "'" & txtSearchBox.Value & "*' OR tblCalls.CustLastName LIKE '" & txtSearchBox.Value & "*')"

This will allow the user to search for a record by first or last name. But if they try and search with both it will not return anything. Can someone help me out? I need to to be searchable by first, last, or both.

Thanks.

Hey SinKoh,
you need a query with the customer first name and last name concatenated and then apply the search box against that computed field.

Best,
Jiri
 
Code:
            SearchStr = FilterSearch & "(tblCalls.CustFirstName LIKE " & "'" & txtSearchBox.Value & "*' OR tblCalls.CustLastName LIKE '" & txtSearchBox.Value & "*' OR tblCalls.CustFirstName + "" + tblCalls.CustLastName LIKE " & "'" & txtSearchBox.Value & "*')"

Like this?
 
Code:
            SearchStr = FilterSearch & "(tblCalls.CustFirstName LIKE " & "'" & txtSearchBox.Value & "*' OR tblCalls.CustLastName LIKE '" & txtSearchBox.Value & "*' OR tblCalls.CustFirstName + "" + tblCalls.CustLastName LIKE " & "'" & txtSearchBox.Value & "*')"

Like this?

No. Create a query based on

Code:
 SELECT  tblCalls.ID, [CustFirstName] & " " & [CustLastname] AS Name FROM tblCalls;

save the query as "qryFullname" and then search on

Code:
"qryFullName.Name LIKE " & "'*" & txtSearchBox & "*'"

Once you concatenate the names, the filter does not need to know whether the portion of the name in the Searchbox is first name, last name or (a portion of) both.

Best,
Jiri
 

Users who are viewing this thread

Back
Top Bottom