From the course: Access 2016: Forms and Reports

Combine text boxes

From the course: Access 2016: Forms and Reports

Start my 1-month free trial

Combine text boxes

“

- [Voiceover] This might come as a surprise, but we can use table data to calculate new values right inside of our forms. Let's take a look at our employee directory, and I'll show you what I mean. Right now, our names are being displayed with this large gap between the first name and the last name. That's because they occupy two separate text boxes. But I can shrink them down a little bit and bring them closer together, but it's not going to be a perfect fix for every employee. Since some employees have short names and some employees have long names, there's always going to be some gap with this approach. Instead, I can merge the first name and the last name fields together into a single text box. Let's go into design view, and we'll open up the Property Sheet over here. Then I'm going to click on this box, here, that's for the first name. On the data tab, we can see where the data is coming from, and we can see in this Control Source property that it's coming from a field in our original data table called First Name. Now before we make a change to this, what I want to do is come over to the other tab here and find the Name property, and we can see that the name of those text box is also FirstName right now. We're going to use the reference to the original data table's column called FirstName in a calculation here in just a minute, but because the name of this text box is also FirstName, it's going to get a little bit confusing, so what we want to do is actually change the name of this box before we begin our calculation. I'm going to change it from FirstName to, say, FullName. Now that that change has been made, we can go back to the data table, find the Control Source property, and make our change. In this case, I'm going to right-click in this box, here, and open up a window called the zoom window. That's going to open up this box here that will give me lots of room to type. In fact, I can even change the font size here so it's a little bit bigger and easier to read. So now let's enter the calculation, and it's going to combine our FirstName data with our LastName data. In order to do this, I'm going to come to the very beginning, and I'm going to type an equal sign, because we're entering in a formula. Then I'm going to wrap FirstName with square brackets, just to refer to that data field in the original data table. Next, I'll type in an ampersand, and this the concatenation character. This is going to join text strings together. Now I don't want to join my FirstName directly to my LastName. I want a space inbetween them, so I'm going to type in a double quote, a space, and another double quote, so essentially what we're doing is joining the FirstName here to a space, then we're going to join that with another ampersand to the LastName field. So in square brackets, I'll type LastName. I'll finish it with a closing square bracket, and that's my final formula. Let's go ahead and say okay to that, and it get populated into this box here. Now we can get rid of the box called LastName, because I don't want to duplicate that information. I'll just press delete on my keyboard to get rid of it. Then, since we're adding a lot more text to this text box, I'm going to make it a lot wider. I'll just click here on the resize handle and drag it open a little bit. At this point, we can close the Properties Sheet and take a look at the results in form view. So now I can see the full name of my employees, and they're combined by a single space, regardless of how long their first name is. By modifying the data from our tables for presentation, we can make the display look much more appealing.

Contents