How do I create an advanced search form?

[Seth Schrock]

I think that I see what you are doing. If I understand it correctly, I still have two text boxes on my search form. I believe that I would be able to enter a partial name into either or both text boxes and then run this query from, say a button's On Click event. So I should be able to enter A in the First Name text box and Ta in the Last Name text box and get everyone whose name starts with A and whose last name start with Ta, for example, Andrew Taylor and Amber Talbert. There would also be only one query instead of the two that I had since this one contains the criteria for both the First Name and the Last Name. Am I correct?

[NeoPa]

Absolutely. Right on the button :-)

Furthermore, this query could form the basis of a report or a form and then show just the records that you're interested in.

[Seth Schrock]

That is exactly what I had in mind. I plan on making a subform in datasheet view with the ID field as a hyperlink to the corresponding record in the Employee form. This part I already know how to do.

Would I be wasting my time in trying to make it so that there would be only one textbox to search from? I would make the criteria be

WHERE txtSearch LIKE [Last Name] & (', ' + [First Name] & (' ' + [Middle Initial])

so that if I put in Ta, it would search for the last name or if I put in , A it would search for everyone whose first name starts with an A? I have a test database already setup at work, so unless you think that this is a crazy idea, I will play around with it tomorrow at work.

[NeoPa]

It wouldn't be wasting time so much, as using a less appropriate mechanism. I wouldn't advise that approach. Databases are designed to work with fields. They are generally clever enough to work with calculated values too, but you lose most of the power of the RDBMS doing it that way.

In simple terms it's a bit like saying for filtering fields the RDBMS filters them at source. You plug in the value and it's handled cleverly within the process. Before the data is even returned to you. When filtering on calculated values it can't do that, so it has to get all the data first and only then can it go through, retrospectively almost, and junk the stuff that doesn't match. Like pretend filtering.

[Seth Schrock]

That makes sense. Thank-you so much for your help. You have fixed two problems: the combo box and the search.

[NeoPa]

You're welcome Seth. It's been a pleasure :-)