How many times have you had to create an expression on a form to place the name field? For good table design in previous versions of Access, you had one text field for last name and another text field for first name. But when you wanted to have the complete name in one field, you had to build a form and then create an unbound text box to place the concatenated field. Well, now in Access 2010 there is a new data type called calculated. And it does allow you to concatenate two fields that are in the same table into a new separate field that can be seen in the datasheet view. The only concession that it has is that the new field must be created based on two or more fields within the same table. Let me show you how easy it is to do that.
We start off with a very basic table of Company information. See illustration below.
Switch to design view of the datasheet and add the new field of EmpName and choose the data type to be calculated.
Once you choose calculated as a data type, the expression builder comes up.
The Expression Elements already lists the table you are using. The Expression Categories will list the fields that you have in that table.
What to type:
- Double click the field of FirstName
- Type in the ampersand (&) which is the concatenating operator.
- Type double quotation marks as they indicate literal characters, press the spacebar and type ending double quotation marks.
- Type the ampersand again to join to that the field of LastName
- Click the OK button
Now the design view looks like this:
Notice that instead of the format property, you now have the expression you created.
When you switch to datasheet view, your table now has your new field.
It really is that easy in Access 2010.