How do I create an advanced search form?

[Seth Schrock]

I'm wanting to create a search form so that I can search for employees using wildcards such as * and then be able to select one of the results and go to that employee's record. It will be searching a whole name field that is in the form of Doe, John A. I want to be able to search for *, John and find everyone whose first name is John or search for Doe, * and find everyone whose last name is Doe.

I'm using Access 2010

[ADezii]

First and foremost, a Whole Name should never be stored in a single Field - this defies the Rules of Database Normalization. The above being strongly stated, you can have a Text Box (txtPartial) into which the User can enter a Partial Whole name either First or Last. A List Box will then contain all possible Matches in the [Whole Name] Field for the Value entered in txtPartial. You can then select an entry in the List Box and because of the Unique [ID] Field; a Form can be opened displaying only that Record. The Logic, in general, is posted below:

Dim strSQL As String
 
If IsNull(Me![txtPartial]) Then Exit Sub
 
strSQL = "SELECT tblEmployees.ID, tblEmployees.[Whole Name], tblEmployees.City, tblEmployees.State, " & _
         "tblEmployees.Zip FROM tblEmployees WHERE tblEmployees.[Whole Name] Like '*" & Me![txtPartial] & "*'"
 
lstMatches.RowSource = strSQL       

P.S. - Before you do anything else, I would follow, and read, the Insight Posted at the Link below:
http://bytes.com/topic/access/insigh...ble-structures

[NeoPa]

Hi Seth.

One of the main points about using a modern database is that you never attempt what you are asking for. If you want data within a field to be treated differently from any other data in the same field, then it should be in a separate field. You can always use the data joined together, but separating the data when stored together is an approach that will only lead you into trouble.

All that said, you can filter data very easily within a database. Example Filtering on a Form and Cascaded Form Filtering can help you with the basic concepts and should give you all you need.

[Seth Schrock]

Well, our main database for work (which is huge and is certainly a modern database) has this feature and my boss wants this feature in the database that I'm creating. The reason that I have a whole name field is that I use it to populate combo boxes. I know that I can combine first and last name fields in the combo box, but then they are way separated from each other to make room for big names. If you know of a way around this, I would use it, but it is the only way that I know of. I do have first and last name fields as well as the whole name field.

I will look into the links that you provided and see what I can do with my individual fields.

[NeoPa]

The solution is relatively simple Seth. The SQL to populate your ComboBox would go from something like the first example to something like the second :

SELECT [FullName]
FROM   [Table]

SELECT [Surname] & (', ' + [GivenName]) AS [FullName]
FROM   [Table]

It would still be a single column (assuming that's what you require of course as a multiple column version is also easily supported).

I recommend you actually read the article ADezii linked to in his post #2. Bosses have a habit of asking for things which are technically not very sensible. It's unfortunate certainly (and you have my sympathy), but it comes with the territory I'm afraid. A good, solid understanding of Normalisation is a must for any database technician hoping to survive any length of time in the discipline. It may even provide you with technical ammunition with which to educate your bosses (It sometimes helps when you can refer them to something written down that supports what you are trying to get across to them).

[Seth Schrock]

I tried your second SQL example and the combo box is empty. In the AS [FullName] part, is FullName the name of a real field or is this a reference for MS Access? For example, my fields are FirstName, LastName, and WholeName. Would my SQL be AS [WholeName] or would I leave it as [FullName]?

I did read Adezii's article. I do try to make my databases as close to normalized as I can, but there the times like this when I can't get features to work without adding the extra field. Once I figure out how to do the combo boxes I can get rid of the WholeName field.

[NeoPa]

Sorry Seth. I shouldn't have implied you hadn't. I must have misread your response a little. No worries.

The [FullName] part of the SQL was actually an ALIAS, rather than a field. As such, you can call it pretty much anything you like, but try to ensure it clashes with nothing else for best results.

I'm rushing now, but if you post exactly what you tried when it came up with nothing I'll think about it for you when I can.

[Seth Schrock]

SELECT [People].[Last Name] & (', ' + [People].[First Name]) AS FullName FROM People;

People is the name of the table in my test database. At first I had put the [] around the FullName, but then Access took them away. I tried taking away the [People] from both the Last Name and First Name fields, but that didnt' fix anything. It was there from before so I just left it for this.

If I sound frustrated, it isn't with you. It is with my lack of knowledge. I really do appreciate your help.

[NeoPa]

I see nothing wrong with your SQL Seth. When you say there is nothing there do you mean nothing visible? Are there 0 entries in the ComboBox?

[Seth Schrock]

There should be about 150 people listed, but none are shown. There is space for them and I can scroll down, but there are no names.

[NeoPa]

Ah, so it clearly recognises the records, it just struggles with getting the data. Interesting.

Can you run the following for me in a query and describe the results for me please :

SELECT [Last Name] & (', ' + [First Name]) AS FullName
     , [Last Name]
     , [First Name]
FROM   People;

If you can, can you also post the fieldname list from the [People] table.

[Seth Schrock]

It worked. The first field is FullName, the second is Last Name, and the third is First Name. The FullName field is in the form of Doe, John.

The fields for the People table are:
ID AutoNumber PK
First Name Text
Last Name Text
Middle Initial Text
Whole Name Text

Okay, I'm an idiot. I just fixed the previous version. The Combo box properties had 3 columns with the first set to 0". I just changed it to 1 column and made it 3" and everything appeared. Now I just have to get the filtering portion done so that I can search.

[Seth Schrock]

I have come up with a solution for my search problem. I have setup two queries that pull the ID, First Name, Middle Initial, and Last Name fields from the People table. The first query has a criteria in the First Name field that is tied to a textbox on my search form. Here is the code:

SELECT People.ID, People.[First Name], People.[Middle Initial], People.[Last Name]
FROM People
WHERE (((People.[First Name]) Like [Forms]![Form2]![SearchFirstTxt] & "*"));

For the textbox, I have run this query in the After Update Event. The second query does the same thing, except that it is for the Last Name search. This allows me to type "Ta" and get all the Taylors, Talberts, etc.

Is the a valid way to do this? Does this break any rules? I do want to do this correctly and follow all the normalization rules and rules of proper database design.

[NeoPa]

That's pretty much the way to go Seth. I was working up to a more complicated query that included the full name, as well as the individual elements. This query could be filtered on any of the elements you choose (as all are available as fields in the query).

Ultimately, how you apply your filtering depends on criteria I'm not fully aware of at this time (the scenario you're in), but I would consider applying the filtering separately from the building of the query. That is just to make life as straightforward as possible for you though. Your solution is fundamentally correct.

Forms a good objects for working with (creating and applying) filters. I'm not sure what you're applying it to or for but Example Filtering on a Form should fill in many of the blanks.

[NeoPa]

I just looked at your SQL again more closely, and I realised it's mostly there using a slightly different approach. See if this helps :

SELECT [ID]
     , [First Name]
     , [Middle Initial]
     , [Last Name]
     , [Last Name] & 
       (', ' + [First Name]) & 
       (' ' + [Middle Initial]) AS [Full Name]
FROM   [People]
WHERE (([First Name] Like [Forms]![Form2]![SearchFirstTxt] & '*')
  AND  ([Last Name] Like [Forms]![Form2]![SearchLastTxt] & '*'))

[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 :-)