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.
This does not work in my Access 2010. Calculated does not show up in the data type menu.
December 1st, 2011 at 7:17 pm
No, it does not appear in my data type menu either. Neither does it work when adding a new field in the Datasheet view. Sad, as it looked the perfect solution. Thanks.
December 22nd, 2011 at 12:05 pm
The database needs to be in MSAccess 2010 format. Yours are probably 2000 or 2002-2003 format.
January 22nd, 2012 at 11:19 am
The “Calculated Field” data type shows up in a new database created in 2010. So, if you open an existing 2007 database in 2010, the new “Calculated Field” data type isn’t available. One work around is: open a new blank database in 2010 and import all of your objects.
March 7th, 2012 at 8:46 am
Does data in the calculated field take memory space, or is it like a query where this field runs the data type and minimal memory is required?
March 7th, 2012 at 8:48 am
Worked perfectly! Thank you.
September 12th, 2012 at 4:08 pm
Worked like a charm! At first I created the new field in Datasheet View then I switched to Design View. I got this error when trying to use the Calculated field, “use add field in the modify fields ribbon”. So I deleted the field, saved the table, closed it, and went back in (this time into Desgin View 1st!). I was able to add the new Calculated field with no problems!!! This is awesome! THANKS!!!
November 12th, 2012 at 10:08 am