Search by first or last name

[John]

I have a form that to search for a customer's name you simply type it in a
drop down box and hit enter and it goes to that record. The drop down list is
from all of the customers names, first name then last name. I know how to do
the reverse and have it listed as last name, first. What I want is the
ability to type in either or and have it search. Is this possible?

 

[Clifford Bass]

Hi John,

You can indeed do it. I have not done so in Access. In another
system, I provided the user a text box into which they would type an optional
last name and then an optional comma and first name. Either name could be
partial. So if you wanted to find all people whose last names started with
"John" you would type "John". If you wanted to find all people whose first
name started with "Pete", you would type ",Pete". Or, you could do both and
type "John, Pete". My code then parsed out the partial first and partial
last name and did the find.

To do it in your existing drop-down box, how about just listing
everyone twice? Once as "First Name Last Name" and once as "Last Name, First
Name".

Alternatively, you could use two drop-down boxes and let the user
choose which to use depending on which name the user wants.

I am sure there are other possibilities.

Good Luck!

Clifford Bass

 

[Gina Whipp]

John,

Try this... Create a combo box. Change the 'YourTable' to your table name
and then copy/paste this in the query. This should allow you to type any
part of either name and get a list of results to pick from. You will need
to add an After Update Event to the combo box to actually find the record
that you want.

SELECT DISTINCTROW YourTable.CustomerID, [LastName] & ", " & [FirstName] AS
CustomerName
FROM YourTable
WHERE ((([LastName] & ", " & [FirstName]) Like "*"+[Enter any part of
Customer's name:]+"*"));

After Update of the combo box...

' Find the record that matches the control if CustomerID is text
Me.RecordsetClone.FindFirst "[CustomerID] = ' " & Me![ComboBoxName]
& " ' "
Me.Bookmark = Me.RecordsetClone.Bookmark

' Find the record that matches the control if CustomerID is numeric
Me.RecordsetClone.FindFirst "[CustomerID] = " & Me![ComboBoxName]
Me.Bookmark = Me.RecordsetClone.Bookmark

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm