LstBox Assistance...Not sure how to word it

[d9pierce1]

Hi all,
I have a table, tblPerson and in that table i have the PersonID, FirstName, LastName, sFirstName, sLastName and some other stuff not related to my question at hand....

I am trying to create a form with a listbox and then a search filter to filter that list.

What I am wondering or asking is, is there anyway to pull the ID, First, sFirst, and Last name into the list box as two records? So I can search for a name wether it be the first name or sfirst name in the search filter.
First would be first name, sFirst would be spouce first name and last for both.... If spouce name was not null....

Id First Name Last Name
1 David Pierce


1 Jonna Pierce
2 Robert Howard
ad so on.

Basicall take the two names from one record and make them two records?
Any assistance would be just fantastic. Have been trying several qrys, list box and so forth but cannot seem to bring both names from a record and make two out of them
Thanks
Dave

[CJ_London]

a listbox does not have a filter property, you need to change the rowsource - perhaps something like this in the search field after update or a search button click event

me.listbox.rowsource="SELECT * FROM myTable WHERE firstname like '*" & me.searchbox & "*' OR lastname like '*" & me.searchbox & "*'"

[Gicu]

Create a union query and use that as the row source for the listbox:
"SELECT PersonID, FirstName As First_Name, LastName As Last_Name FROM tblPerson
UNION
SELECT PersonID, sFirstName as First_Name, sLastName As Last Name FROM tblPerson;"

Cheers,

[d9pierce1]

Thanks Gicu,
That worked however,....get ready....LOL
Is there a way to remove any null in the second SELECT?
Meaning there will always be a FirstName and LastName.... but a lot of the time their wont be a sFirstname, sLastName so that shows up a blank record.
thanks again

[d9pierce1]

Figured it out
Thank you everyone

SELECT DISTINCT tblPerson.PersonID, tblPerson.Firstname AS FirstName, tblPerson.LastName AS LastName
FROM tblPerson
UNION SELECT DISTINCT tblPerson.PersonID, tblPerson.sFirstname AS FirstName, tblPerson.sLastName AS LastName
FROM tblPerson WHERE sFirstName IS NOT NULL;

[Gicu]

Cheers,