Easy Search Form 2
By Richard Rost
2 years ago
Zero-Code Multi-Field Search in Access, Part 2
In this Microsoft Access tutorial, I'm going to teach you how to make a simple search form where you can search multiple fields. In Part 2 we'll learn how to use a little trick with Null Math to do an OR search and ignore any empty text boxes as parameters.
Members
Members, I haven't forgotten about you. We're going to do something a little bit more advanced. We're going to add a relevancy score to each hit, and then we'll sort it based on relevancy. So, for example, if you're doing an OR search and you want to show all the records that meet any of the three criteria, we'll add a score - one, two, or three - with the highest score being the ones that match all three criteria. So that'll be pretty cool.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, search form, multi-field search, Access query design, OR search, AND search, null math, query criteria, wildcards in Access, ignoring empty text boxes, database search techniques, record filtering, Relevancy Score
Intro In this video, I will show you how to expand your search form in Microsoft Access by adding additional search fields and allowing users to choose between AND and OR searches. We will talk about the difference between combining multiple criteria with AND versus OR logic, and how to adjust your queries to handle each type. I will demonstrate how to handle empty fields using a technique called null math to make your searches more flexible, and discuss best practices for building user-friendly forms that display your search results. This is part 2.Transcript Today is Part 2 of my Easy Search Form video series, Part 2 of 2. We're not going to do too much with this one. But it is Part 2, so if you haven't watched Part 1 yet, go watch that one and come on back.
We now join the program already in progress.
We left off with getting first name working. We hit the search button, boom. There's everybody with RI in their first name. Now let's do the other fields.
You have a question to answer. Do you want it to be an AND search or an OR search? In other words, if you type in criteria here, do you want the results to have to match all of them (in which case it is an AND search), or any of them (in which case it is an OR search)?
Let's do an AND search first.
Design view: we're just going to basically take this criteria here. I'm going to zoom in. We're going to copy this. Come over to the next criteria under last name, zoom in again, paste it in, and just change this up so it's: last name search. Hit OK.
Come over to email. Zoom in again, paste it in, and this is going to be email search.
Now, fortunately, the way that these wildcards work is if they leave it blank, then you don't have to worry about it. You're just going to get ** (star star), which is basically "show me everything." And that's an AND search. So close that.
Let's hit search. There are all the RIs. Let's say now we're looking for somebody with an O in their last name. I'll put an O in here and now I get just that. There are the RIs and the O's.
How about someone with, let's say, a five in their email address? This is how you can narrow it down. There we go. I narrow it down to just the paragraph and so forth.
But what if you wanted that to be any of those conditions? Well, we're going to make two queries, because then we can change it up and have different options for the buttons.
Let's take this customer search, copy, paste it, copy, paste it, copy, paste it. You have to do it fast. Sometimes, and this is a note for the Access developer team, sometimes if you copy and wait too long, it won't let you paste, even if you use the keyboard (control C, control V). But anyway, copy, paste it.
We're going to call this one the customer search 2. This will be our OR search. Design this guy.
What do we do to make this an OR search? We move these down a row - across or down. This means this has to be true AND this has to be true AND this has to be true. If we move it down, then it's OR: this has to be true OR this can be true OR this can be true. Across or down - don't forget that.
So we're going to just cut that out, paste it there. Use the arrow keys up, cut it out (control X), down, down, paste it there. Now, same criteria, but any of those conditions can hit.
Unfortunately, with this one, you're going to run into a situation where you have to put criteria in each of the boxes. Otherwise, the OR for this is going to give you all the records.
If I save this, now let's change this button up a little bit. Design this guy. This will be our AND search. We'll make another button for our OR search.
Again, buttons - drop that down here. Miscellaneous, Run Query, customer search 2. This will be our OR search.
Next, we'll do this. What did we call the other one? Search button 2. We'll call this one.
There's our AND search and our OR search. Save it, close it, open it.
Now, if I put an R in here and hit the OR search, you're going to get all 33 records, even if someone doesn't have an R in here, because of those stars. So you have to have something in each field. Let's say an R in that field, an R in this field, and a 5 in that field and do an OR search - and any of those are true, you're going to get the hits. You can see here there are only 28 records, so there are a few records that don't match that criteria.
You might be thinking to yourself, Rick, I don't want to have to put something in each field. I want to be able to put just an R in here and find just the first names of the R's. Or maybe just an R there and an R there. I'm going to get all the records anyway because the email isn't there. Isn't there a better way?
Yeah, it's kind of tricky, but there is. It involves a little trick with null values. Now, null values are values that basically don't exist. They don't have any value. Right now, email - this box here is null. It's empty. Well, empty and null are technically two different things, but for the purposes of this class, for a beginner lesson, it's null.
We can basically tell Access: if this box is null, ignore it. Don't use its criteria at all.
How do we do that? Let's come into our query here and design view. Let's take a look at this guy. Instead of using concatenation (add these things together with an ampersand), use a plus on both ends.
Normally with concatenation, it says take this asterisk, put whatever is in here next to it, and then put another asterisk at the end. If this turns out to be null, if there's no value there, you still get the asterisk and the asterisk, which gives you all the records.
But with the plus sign here, plus says add these values together mathematically. The rule in Access and pretty much any other database that uses SQL: if you add a null value to anything, the answer is null. Null is basically saying "no criteria here," so it ignores the whole thing.
It's a neat little trick and it's called null math. I've got a whole separate video on null math if you want to go watch it and learn more. We can use this for example - if last name doesn't exist and you add it in here, the space doesn't exist then, too. But with the concatenation, you'll still get the first name. There are all kinds of tricks you can play with this. This is the only way you can do this without VBA or some really crazy functions.
I'm going to hit OK there. We'll do the same thing with the other two. Put a plus in here, put a plus in there, and then for here, you'll want to put a plus in there and a plus in here.
Save it. Close it.
Now, if I just put an R in first name and do an OR search, look at that. I got 11 rows. There are all the OR's. These other two fields basically get ignored. There's no criteria in them.
If I put nothing there and I put an R in last name and hit OR search, I have an R in them. There are 22 records, lots of R's in there. The other ones are ignored.
I can do two OR's and do an OR search. Now I'm getting a lot more records. This field can have an R in it OR this field can have an R in it.
Let's do an O in the first name. There, an O or an R. If you find somebody here, let's see who's got an R in the last name. Perfect. O in the first name, and they don't have an R in the last name, but it's an OR condition. That works out perfectly.
You can also tag an email address. If you put a five in here, now you'll pretty much get everybody. No, no 25, you get nobody. Everybody.
You see how that works. So using null math, now we can do an AND search or an OR search, and they'll come out accordingly. If you leave off these criteria, then it just ignores those and doesn't give you any data from those. It doesn't use those fields as part of your criteria.
If you really want to make this user friendly, especially if you have users who are beginners, I strongly recommend that you don't just give them the query. I don't open the query. I would recommend making a form, like my customer list form, like this thing here.
Have this form get its data from this query and display this information in a form. That's recommended. Go watch my blank template video where I show how to make the customer list form. You just make that form based on this guy and use open form instead of open query to display it.
Then you can do neat stuff, like double click on the customer ID and open up the customer form directly.
Now, members, I did not forget about you. I've got some cool stuff coming up.
Let's take this one step further. Let's make a relevancy score over here. What we'll do is we'll add up which ones of these fields they have a hit for. So if they match this, then they get one point. If they match last name, they get a second point. If they match email, they get a point.
So this one here, Jean-Luc, matches all three, so you get a relevancy score of three. You might want to search for people based on these criteria and say, "Whoever matches most of these criteria, put them up toward the top of the list." We'll do that in the extended cut video.
Extended cut videos are a little bit extra, a little bit something more for the members. It's how I get you to join my channel, join my website. Become a member, Silver member and up, you get access to all of my extended cut videos. There are lots of them, folks, hundreds of them, lots of stuff to watch. It's only six bucks a month, as of now, as of 2024. I don't know if it's going up in the future. As of right now, I would rather keep it at the same price. I would rather have more members and keep the price low. As long as we keep adding members, I'll keep the price right where it is.
If you like learning with me, if you like this stuff, come check out my Access beginner courses. I have lots and lots of beginner courses. These are very inexpensive. They teach you all the basics, everything you need to know. We do main menus, combo boxes, reports, and all kinds of stuff.
After that, you go up to the Expert series. I have lots of Expert lessons where we get more into relationships, go through all the different functions, build invoices, and all kinds of crazy stuff. If you want to learn Access with me, this is the place to do it.
That is going to be your TechHelp video for today. I hope you learned something, folks. Live long and prosper. I'll see you next time.Quiz Q1. What is the difference between an AND search and an OR search in the context of search forms?
A. An AND search requires all conditions to be met, while an OR search requires any condition to be met
B. An AND search returns no results, while an OR search returns all results
C. An AND search only checks the first field, while an OR search checks all fields
D. An AND search sorts the results, while an OR search filters randomly
Q2. In Access query design, how do you set up an AND search using multiple criteria?
A. Place all criteria on the same row
B. Place each criterion on a different row
C. Use only one criterion at a time
D. Set all criteria to null
Q3. How is an OR search set up in the query design view in Access?
A. Place each criterion on a different row
B. Place all criteria together in the same field
C. Enter all criteria on the top row only
D. Use only the last field for criteria
Q4. What is the purpose of using wildcards like asterisks (*) in Access search forms?
A. To match any characters in the search criteria
B. To automatically sort the records
C. To prevent any results from appearing
D. To force the user to enter all fields
Q5. What problem can occur with a basic OR search when only one search field is filled in?
A. It returns all records regardless of the criteria because of how wildcards are processed
B. It returns no records at all
C. It ignores any null values in the query
D. It produces a run-time error every time
Q6. What is the "null math" trick used in Access queries?
A. Using a plus sign (+) for concatenation so that if any part is null, the result is null and the criteria is ignored
B. Using a minus sign (-) to remove null values from the criteria
C. Using multiplication to combine field values
D. Using ampersands only to concatenate strings
Q7. In Access, what does concatenating with an ampersand (&) do when null values are present?
A. It treats null as an empty string and concatenates the rest, resulting in wildcards that match all records
B. It ignores the field completely
C. It generates an error message
D. It restricts the search to only complete records
Q8. When using the "null math" method, what happens if a search field is left blank?
A. That field's criteria is ignored in the search
B. The query fails to run
C. All results are hidden
D. Only that field is displayed
Q9. Why is it recommended to use forms instead of letting users open queries directly?
A. Forms are more user friendly and allow more control over user interaction
B. Queries are always slower than forms
C. Queries cannot display results at all
D. Forms are required for all Access databases
Q10. What is a "relevancy score" as introduced in the video?
A. A value indicating how many criteria a record matched in the search
B. The number of times the database was queried
C. A rating given by users to their favorite records
D. The length of the first field in a record
Q11. What advantage does the "null math" technique provide compared to basic OR queries with wildcards?
A. It allows the user to enter criteria in only the fields they want without returning all records
B. It makes every search an AND search by default
C. It guarantees that null fields will never be matched
D. It sorts the results by default
Q12. What is the main takeaway for making beginner-friendly search forms in Access?
A. Use forms as interfaces for queries and handle null values smartly for flexible searching
B. Always use direct queries without forms
C. Only offer preset searches to the user
D. Avoid using wildcards at all costs
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone will continue our series on building an Easy Search Form in Access. This is Part 2 of 2, so if you have not seen Part 1 yet, I recommend watching that first to ensure you are fully up to speed.
When we last worked on our search form, we focused on being able to search by first name. Entering a value and clicking the search button filtered the records as expected. Now, we will expand this to include the other fields: last name and email.
Before setting up these additional fields, you will need to decide: do you want your search to be an AND search or an OR search? An AND search will display records that match all of your entered criteria at once, so for example, the records returned must meet all conditions you set in each field. On the other hand, an OR search will show any records that meet any of the given criteria.
Let's start with the AND search. In design view for your query, you can simply copy the criteria you set up for first name and paste it into the criteria row for last name. Make sure to adjust the field reference so it matches last name search. Do the same for the email field. The important detail here is that the way wildcards operate, if a search box is left empty, it essentially shows all records for that field, so users can choose which fields to filter by.
You can then test this by entering values in various combinations and seeing that the query returns only the records that match all criteria provided. For example, you might enter 'RI' for first name and 'O' for last name. The query will only return those records that meet both conditions. Add a number in the email field, and you will further narrow down the results.
But what if you want your search to function as an OR search, where any of the conditions can be met? The process is a bit different. The simplest way is to make a copy of your customer search query and adjust how the criteria are positioned in the design grid. In Access, placing criteria on the same row means all conditions must be met (AND), while placing them on separate rows means that any can be met (OR). So, move your criteria down to separate rows for an OR search.
Now, a limitation of this technique is that if you leave any fields blank, you risk retrieving all records, because blank criteria combined with wildcards will match everything. To address this, you may need to enter something into each field to get an appropriate result set, which may not be the user experience you want.
You might be wondering if there is a better way to handle this; in other words, is there a method that allows users to enter criteria in only one field and get just the relevant results without returning all records? There is, and it involves working with null values in Access.
In Access, a null value is one that doesn't exist. If a user leaves a box empty, it is null. You can modify your query criteria so that if a control (like your search field) is null, the query simply ignores its criteria for that field. This involves a trick often called null math. Instead of using the usual concatenation with ampersands, use the plus sign in your criteria expressions. This way, if one part of the expression is null, the whole result becomes null, and Access ignores it as a criteria.
By implementing this change in your query's criteria for each field, you allow users to filter by any combination of fields they wish, without needing to fill out every box. For example, if only first name has a value, only records with that value in first name will be retrieved, regardless of the other fields. If only last name is filled in, only last names are filtered.
This setup makes your search form more flexible, enabling both AND and OR searches that behave as expected for the user. Fields that are left blank simply are not considered in the filtering process.
For a more user-friendly setup, especially for novice users, I suggest not exposing raw queries. Build a form that relies on the query as its data source. This way, users interact with a form rather than directly with the query. I cover how to do this in my blank template tutorial, where I explain how to set up a customer list form that retrieves its data from your search query and presents it in a user-friendly format.
Additionally, advanced functionality such as double-clicking a customer ID to open a detailed form can make your database much more usable.
Now, for those of you who are members, there is additional material available. In the Extended Cut of today's lesson, I will show you how to add a relevancy score column to your query. This score can help you identify which records match the most criteria by tallying up the number of fields in which a record matches your search. For example, a record that meets all three criteria will have a score of three, which allows you to sort or rank your results based on their relevance.
Extended Cut videos are a special benefit for members. If you are not a member yet, becoming one gives you access to hundreds of extra lessons, all for a low monthly fee as of 2024. I always try to keep it affordable and add as much value as I can for members.
If you are interested in learning more about Access, I also offer a broad range of beginner and expert courses. The beginner courses cover all the foundations, including how to work with forms, combo boxes, reports, and more. The expert courses take you through more advanced database management, relationships, functions, and complex tasks like building invoices.
That wraps up today's TechHelp tutorial. If you want a detailed, step-by-step demonstration of everything we discussed, you can find the complete video lesson on my website at the link below.
Live long and prosper, my friends.Topic List Adding search capability for multiple fields
Implementing AND searches in Access queries
Implementing OR searches in Access queries
Duplicating and modifying queries for search modes
Using wildcards in search criteria
Positioning criteria for AND versus OR logic
Button setup for different query searches
Handling null values in search fields
Using null math to ignore empty criteria
Modifying queries to support flexible OR searches
Making user-friendly search forms in Access
Connecting a form to a query for results display |
