Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
You will sometimes find it useful to take the data from two separate fields and merge it into one field. For example, a well-designed database has separate fields for Last Name and for First Name. But there are times when you want to have that person's full name in one field. Here is how to do that. I am going to open up my SalesReps table and move it into Design View. First I need to add the new field. I will right-click on the gray box to the left of Address and choose Insert Rows. I will type in capital F, Full, capital N, Name (FullName) with no space.
For my Data Type, I will drop it down and I will choose Calculated. Immediately, the expression builder appears. Double-click on the First Name in the Expression categories column and it will appear at the top. To add more text to the string, we need an ampersand or an and sign, go ahead and type it to put it in a space, type a quotation mark, and a space and then a quotation mark again. The quotation mark tells Access to add on whatever is inside them.
In our case we need a blank space between the FirstName and LastName. Next we need another ampersand to connect the LastName to the string ([FirstName] &" "& [LastName]) Now go back to the Expression Categories and double-click on LastName. When I click OK, I can now see my calculation in the Field Properties on the Expression line. You string should look like this, bracket, FirstName Bracket, ampersand, quotation mark, space quotation mark, ampersand(&), bracket, LastName bracket.
Typo's matter, so make sure everything is exact. When I am done, I will go back to the data sheet view, it will ask if I want to save the table and of course I will say Yes and now look I have a new column called FullName. I will hold my cursor over the line between them and double-click to stretch it out and now I have Jordon space Hinton, Lilah Douglas. And what's nice is if I make any changes to my data, they will immediately show up in the calculated column. Concatenating fields can also be used to add text after a number.
For example, if you want to put the word inches after a measurement or anytime you want to combine data from several fields.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64672 Viewers
80 Video lessons · 124298 Viewers
52 Video lessons · 60237 Viewers
59 Video lessons · 46066 Viewers