Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today, we're going to see how to search multiple fields across multiple tables using just one search box. See, I make a little box on a form here. You type in what you're looking for and you hit the search button, and then it can search across first name, last name, product name on an order, order ID, any field you name it; you can search on it. I'm going to show you how.

Today's question comes from Harrison in Melbourne, Australia, one of my platinum members. Harrison asks, "How can I set up a search in Microsoft Access so that I can type into one box and it will look through multiple fields like first name, email, or address all at the same time?"

I do the same thing in my database. I have a box on my main menu where I can type in any value. It'll search on email address, customer ID, order ID, first name, and last name, and a few other fields I forgot about. But we can do the same thing very easily in pretty much any Microsoft Access database.

Now, before we get started, this is an expert-level video. Expert is what I consider between beginner and developer. So we don't need any VBA code to do this. We can do this with just some query criteria, a couple of little tricks, and a query and a form if you want.

But there are a few things you should know beforehand. For example, you should know how to get a value from an open form. We're going to put a search box on our main menu. We need to know how to refer to that by name. You should know what query criteria are and how to perform a wildcard search using the like keyword and the wildcard characters like the asterisk. You should know the difference between and, or, and not in your query criteria and the and across or down rule. If not, very important, go watch this guy.

You should understand how to make command buttons at least. We're going to make a command button using the wizard to open a query or to open a form. Go watch this video for a little more information on that. And optionally, you should understand string concatenation, that's smashing multiple strings together, or basically any multiple fields together. This is a basic one; you should have this in every Access developer's toolbox.

And again, optionally, I'm going to make a search box to search between customers and their orders. So if you want to see how I built my order entry system, go watch my invoicing video. These are all free videos. They're on my website, they're on my YouTube channel. Go watch them if you want, and then come on back.

I've done a lot of different videos on searching and finding stuff like this one and this one and this one and this one. In fact, I've got a whole separate search seminar on tons and tons of different ways to perform searches. And that's because there's lots of different ways to search for stuff in your Access database. And there's lots of different techniques because different techniques work for different people for different styles.

So today, we're going to make a quick search box, and then a query is going to basically search for all the fields using that criteria.

All right, so here I am in my TechHelp free template. This is a free database you can grab off my website if you want to. So on my main menu, I've got this "Today is" box. It's got the date in there. We're going to just repurpose this as my search box. Then we'll make this button our search button.

So I'm going to slide this over just a little bit, and I'm going to type in the word "Search" in here as a caption. And we'll just move that over to the left a little bit. Let's see. Come here.

And then this guy will make our search box. Let's open up the properties. I'm going to rename this box. Let's call it "SearchText." I'm going to get rid of the control source because right now it's set to =Date(), which means it puts the current date and time in there, and you can't change it. It's always set to the date. Get rid of that, get rid of the format because we're not typing in a short date. Get rid of that, and that should be good. We got a nice blank box there called SearchText.

And we'll deal with this button in a minute. But let's just save this and close it and then reopen it again. Now we got our little search box here.

Now we're going to make a query that's going to bring in all of the fields that we want to search on. Let's say we want to search on first name, last name, email, address, phone number, anything that your user might search on to try and find a customer. And you can also add related data from other tables. Let's also add in the order information, how about what product they purchased. So if they said they, you know, they purchased — you can't find them in the customer table, maybe someone's wife bought it or whatever, but they did buy a phaser bank, right? So you could search for the people who bought phaser banks, or their order total — "Hey, here's my invoice. It's ripped in half. I don't have the order number, but the order total is 472.53. Should be easy to find," right? You can use any number you want.

Now I already have a query called CustomerOrderQ that brings together the customer, the order, and the order details, and you can see what that is here. I covered this in my invoicing video, right? Customer, Order, Order Details. So this has pretty much everything we wanted in it. We could just get a couple of customer fields. There's the product name, right? There's the line items for each order. And we just have to add a few fields in here. We got first name, last name, address. We just got to add phone and email to this. So design view, let's add in phone and email.

Now people always ask me, "Is it okay? Is it safe to go back and modify older queries if you got other stuff based on them?" Well, hey, be careful, right? Generally, adding fields doesn't mess things up. Don't delete fields unless you're absolutely positive they're not being used anywhere. I typically, in bigger databases, try not to touch the existing queries unless they're relatively new because you don't want to mess up something that's been around for a while and oops, right? Always backup, backup, backup, backup.

Now I like to keep all the fields from a single table together. So I'm going to select both of these guys, let it go, and then grab them and drag them to the left and put them right here with the customer stuff. Just so all the customer stuff appears together, right? All the order stuff, all the detail stuff. So this is good. We're done with this. Save this.

Now I'm going to make a second query that just has the fields in it that we want to search on. No sense in having all the rest of that stuff. So we're going to go to Create and then Query Design. I'm going to bring in that CustomerOrderQ because it's got everything we need in it, right? Just like this.

Now we're going to bring in the fields and only the fields that we care about. So if you want CustomerID, even though we're not searching on it, you can bring it in. I have longer customer IDs in my database because I've been around for 20-some years. So my customer IDs are up to like 32,000, whatever. So I can search for a number like 32146 and it'll generally come up with a customer. Like order IDs are much, much larger than that now. But if it's a new database like this one, all the customer IDs are pretty low. So if I just search for a 7, it could bring up anything. And obviously, if you have custom part numbers or custom order numbers or whatever that you design, that's even better. But searching for low IDs usually doesn't help, but if you want this so you have it in the results, that's fine. There's nothing wrong with that. Just like you can also bring in the OrderID if you want.

But I'm just going to bring in first name, last name, address, phone, email, and the stuff we're searching on. And then we'll bring in, what did we want? We want the product name and the price. Okay, we're doing unit price, that's fine. If you've got another query that's got all the order totals, I've got one that's got all the order totals in it, the Order Summary Q, we could bring this into. But that's not necessary, you get the point, you see what I'm doing here. Save this as my SearchQ, this is my search query, and if I run it now, I get everybody. There's everybody in the original query.

Okay, now we can use our criteria from this guy here, right? From this FormsMainMenuF, that's the name of the form and then SearchText. So we can put that criteria in here now. We have to use wildcards, right? Otherwise, it'll be an exact match. If I just type in =, let me zoom in so you can see this better, Shift-F2, whoops, Shift-F2. Right, if I just type in = FormsMainMenuF and then it's SearchText, that's an exact match, it's got to match it exactly, right? But if I put the wildcards around it and I say like * and that and a *, then it will match any number of characters in the beginning, any number of characters on the end as long as the search text appears within that, right? That's how you use the Like. And we have another video for that, which you're supposed to have watched. All right, so there's my criteria. Now that's for first name.

All right, let's save this. If I run it now, I still get everybody because there is no criteria here. So like ** is basically like everything. Now let's say I put, let me go back into design mode here. Let's say I put in here, right, Rost, and then I run this. I'm still getting everybody. Why is that? This is one that gets a lot of beginners especially. Well, the reason why is because if you look very carefully, I never left that field, right? I just typed in Rost, then I came over here and ran the query. This hasn't saved yet. Data doesn't get saved in fields until you move off of them. Now I just hit Tab. Now if I run that, you can see — whoops, it's not working, what happened? Let's take a look. Well, oh, I'm in first name. I'm only in the first name field. I didn't put any criteria in last name. Okay, that makes sense.

All right, so now I'm going to type in "Ric" and I'll hit Tab. You're not going to have to hit Tab in the final version, just be patient, right? And now I'll run this query and okay, there I go. There I go, I got all the Rics.

Okay, all right, we're getting somewhere. Now the next thing we have to do is take this criteria and put it in all the fields. But we don't go straight across, right? In fact, we can just copy and paste this, right? Copy. Don't put it here because if you put it here, remember it's and across or down. This means it'll have to be the same criteria in all of the fields at the same time. So we're going to go down a row, paste it there, down a row, paste it there. See, it's or down. It's got to be this or that. I'm running out of room here, so let me just do this, right? Paste, paste, paste.

Okay, so now we've got it in all of the fields, but it's an or condition. So we can have a phone number that's got that or a product name that's got that or a unit price that's got that. Okay, let's save it. And let's try some more stuff here. Let's come up here and type in PH as for phaser. I'm going to hit Tab, come over here, run it, and there you go. There's your two orders containing a phaser bank. Right, let's search for a partial phone number. Let's do — well, let's go back here. Let's get rid of that criteria so we can see everybody. Run it. All right, let's do a search for a 3367. It should come up with his phone number. All right, 3367. I'll hit Tab and then we'll refresh the query. You can just go over here and hit F5 and look at that, it reruns it. Oh, look at that. All right, so the query is working.

Now the last step is we just have to make a button that opens up this query. I'm going to show you how to do it two ways. I'm going to show you how to do it with the wizard, and then I'm going to show you — oops, someone's beaming in, is it more tribbles? I'm going to show you how to do it with the command button wizard, and then I'll show you how to do it with one line of VBA code, which in one line is really easy. All right, form design, find a command button, click on it, drop it down here somewhere, the wizard starts up. It's going to be miscellaneous and then run query. Next, which query are we opening? The SearchQ. Next, put whatever text you want on it, run query, that's fine. Next, give it a meaningful name like "SearchButton," whatever you want to call it, and then hit finish. And there you go. There's your run query button.

Okay, save it, close it, come back over here. Type in ROST, run the query, and, boom, it works. See? Let's search for a hyperdrive this time, H-Y-P-E, and then run it, and then boom, there's your hyperdrive and your hyper spanner. See? And you don't have to worry about tabbing off of that because as soon as you click on the button, this box loses focus. It's called focus when you move from field to field, and so that problem's taken care of. It was only a problem before when we stayed in the search box.

All right, now I told you I'd show you how to do it with VBA. Now VBA is not scary. Watch how easy this is. I'm going to get rid of this button. We're going to reuse this button here that's already here. It's just a blank button. It's got a little bit of stuff in it right now, but don't worry about that. Let's just change the caption. This is going to be the search. I'm going to right-click on it, go to build event. That opens up this code builder here, and you can see I'm in the — it's called my HelloWorldButton. I'm in the click event for the HelloWorld button. Watch this. All I have to do is come in here and say, "DoCmd.OpenQuery SearchQ." That's it. That's all you got to do. See? That was much faster than even running the command button wizard. Right, VBA is super easy. You really just got to know a handful of commands, and you can take your database to a whole other level. If you want to get started learning VBA, go watch this free video. It's about 20 minutes long; it teaches you everything you need to know to get started.

All right, so now I can close this. I can close this. Save changes.Yes, click on this and then I type in anything here. Like a lot of know John and then search, and there it is, John Leap of Card. Nice and easy. Now I've got his custom ready. You can add order ID in here if you want to, and if you don't want to see a query.

I generally don't like my end users accessing tables or queries directly. So, what you do is you take a form and base a form on this query. The form gets its data from the query, and then when you open the form, the query runs in the background and feeds the form. It's that simple. I have several other videos on how to do that.

I'm going to show you one more quick thing. Sometimes what I find is easier, instead of doing it like this with a whole bunch of criteria, is this: I'll concatenate all these fields together into one and then just put the criteria on one of them.

For example, come over here, and I'm going to make a new field here. I'm going to Shift+F2, zoom in so you can see it. I'm going to make this. We'll call it the search field, and this is going to be the first name, the last name, the address, the phone number, the email, the product name, and the unit price, just mashing together all the fields. You're searching it. It doesn't matter; you don't need spaces between them or anything else. Just mash it all together in one field.

Now you get all the data you're looking for smashed together. That's fine, that's okay. But now it's so much easier because now you just need to do this: watch, take one of these and put it there, and I can get rid of all the rest of them. Delete, delete, delete. Okay, there's one to delete.

Then you can go like this and squeeze all these guys down, and just the criteria in that one field. And if you don't want to see it in the results, fine. Just hide it. That's okay. Turn that off, save it, close it, and watch this. Same results. Instead of putting the criteria on all of those fields, you just mash all the fields together and put the criteria on at once. It's just doing a simple text search. Obviously, this wouldn't work with something if you're doing like a range search, but just looking for text, it works just fine. That's what I generally do, and it's simpler SQL too.

Now, in the extended cut for the members, we're going to take this one step further. We're going to do a search with VBA. First of all, we're going to say, "Alright, if you find a customer, open the customer form right to him." If you find a product on an order, open up that order automatically instead of just giving the user a list of different options and they still have to go hunt it down.

We'll cover this in the extended cut for the members. Silver members and up get access to all of my extended cut videos and a whole bunch of training. And don't forget, if you want to learn lots more about searching in a Microsoft Access database, I've got a whole thing here called the Search Seminar. I'll put a link down below. It's got lots of different stuff in it, including a full natural language search and search as you type, in detail with spaces, so it's really cool stuff.

But that is going to do it for today's TechHelp video, folks. I hope you learned something. Live long and prosper, my friends. I'll see you next time.

TOPICS:
Search multiple fields using one search box  
Create a search box on a form  
Get a value from an open form  
Set up query criteria for searches  
Perform wildcard searches using 'like' and '*'  
Refer to form controls in query criteria  
String concatenation for searches  
Modify an existing query carefully  
Create and run a new search query  
Add fields to be searched in a query  
Set up a command button to run a query  
Use VBA to open a query with a button  
Concatenate fields for a simpler search  
Hide fields from query results

COMMERCIAL:
In today's video, we're going to talk about creating a search box in Microsoft Access to efficiently search multiple fields across multiple tables with just one input. Learn how to set up a dynamic search box on your form, leveraging query criteria and wildcards to scan fields like first name, email, and address. I'll guide you through modifying and creating queries and setting up buttons to execute those searches seamlessly, even without using VBA. We'll also explore a quick method to concatenate fields for simplified searching. Plus, you'll hear about more advanced tips in my extended cut and Search Seminar. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.