From the course: Office 365: Access Essential Training

Searching and replacing values - Microsoft Access Tutorial

From the course: Office 365: Access Essential Training

Start my 1-month free trial

Searching and replacing values

“

- No text editing environment would be complete without an easy way to search through the text and make replacements. Access is no exception, and we can use the same Find and Replace tools that you might be familiar with from other Office programs. Once again we're going to double click on the Guests table here in the Navigation pane to open it up, and I want to go ahead and try and find a few things here in the data tables. Now the first thing we can do is use this Search box at the very bottom of the screen here that's next to the Filter button. If I click in the Search box and go ahead and type in Columbia, you'll notice that it jumps straight to the first person that has Columbia.edu in their email address, which would be this Norma Ray at Guest ID 151. Let's go ahead and double click on this field edge right here to increase the width of the Email field. Now if I wanted to find somebody else, I'll just click back down here into the Search and press Enter. You'll notice that it finds Norma Ray one more time, and then if I press Enter again, it jumps to Mark Ferguson, who is Guest ID 190, who also has a Columbia.edu email address. If I press Enter again, it doesn't find anybody else, so it looks like we only have two people with a Columbia word in their email address. Now there's other ways that I could search, though. If I come up here to the ribbon at the very top of the screen, you notice I have a magnifying glass icon that says Find. And when I click on it I get the Find and Replace dialog box here. Find and Replace has two different tabs, one is Find and the other is Replace. Here inside the Find tab we can find the text that we want to search for. I can use a dropdown list and look at other search times that I might have already searched for here. We also have options, we can look in either the current document, which is the default here, so right now we're searching through the whole document. Or we can specify we want to look in at just a single field that we might be currently selected in. I'm going to go ahead and leave it on Current Document. The next option is to match, and right now it says any part of the field. But we also have the options to either match to the entire field, or just something that begins at the beginning of the field or the start of the field. Again, I'll just leave it on the default here. And then finally we have the Search one that says All, or we can search Up, or Down from our current cursor position. We have a check box here to Match Cases, so if I search for Columbia all lowercase that will find something here. If I click that Find it will go back up to Norma Ray. But if I were to say, Don't match case, and I make this capitalized, you'll notice that it will find the next person, but then again if I do Match Case then it won't find anybody here because it's trying to match Columbia with a capital C, and it's all lowercase here in our data table. That's what this warning message is telling me here so I'll just go ahead and say Okay to that to dismiss it. So that's how you would find something. We can also go through and replace things, so if I switch over to the Replace tab, looks like we have some different options here. And in fact, if I close out of this window and then go up here to the ribbon you'll notice I also have a button for Replace. If I click on that it just brings up the same Find and Replace dialog box and it just puts me in the Replace tab instead of the Find tab. So once we're here we can put in the text that we want to find, but then we can also specify text that we want to replace it with here in our Data table. So for instance, here, maybe I want to take the Name United States, and replace it with United States of America. So what we can do here is go into Find what and I'll type in United States. And in the Replace with section I will type in United States of America. Once again we can look in either the Current Document or just the Current Field. In this case I think the current field is going to be email, I think that's where my cursor was when I came in here. That's not going to work so I'm going to make sure we're searching through the entire document. We can also search any part of the field, or the whole field, or the start of the field. I'm going to choose Whole Field just in case United States happens to show up in somebody's email address, for instance, or in another field that I'm not anticipating. So I want to make sure we're matching to the whole field. Again, we could specify to match case, or not match cases. And in this case I'm going to Match Case just to restrict it to the exact way that I've got the capitalization up here in the Find what section. When we're ready, we can go ahead and press the Find next button and it will find the next one from where I started. If we want to replace that you just press the Replace button and it will move onto the next one. And we can keep pressing Replace here to replace them, or I could just press Replace All, in which case it will go through the entire table. It's going to tell me here that I won't be able to undo this replace option, and it asks if I want to continue and I do, so I'll press Yes. And it will go through my data table and it will replace everything that said United States with United States of America. Let's go ahead and close this down and I will expand my Country field by double clicking here on the line between Country and Street Address. That will expand the column and now I can see indeed we have changed everything that said United States to United States of America. So that was quick and easy. The Find and Replace tools make it a snap to locate specific records, as well as edit out common inconsistencies in the data.

Contents