Earlier we looked at a simple way to split a field into two values in Access. In that post I mentioned that it is considerably easier to put fields together (concatenate) than it is to take a field apart. Let's take a quick look now at how to do that.

We'll start by creating a table called example_name in Access with the fields 'first_name' and 'last_name', and I'll enter in a couple of examples:

Create a new query in design mode, add the table you just created to the query, and pull the fields first_name and last_name into the query grid (this is also explained in detail here). When you're done you should have something that looks like this:

Combining (or concatenating) two or more fields in a query grid is accomplished very simply by using the & operator. All you need to do is to reference the first field using the [] square brackets, add a (&), then a space by using an empty quote string (" "), add another (&), then add the second field.

So, start by typing in a name for the field (we'll use whole_name), then a colon:, then the string from above. When you put it all together it will look like this:

whole_name:[first_name] & " " & [last_name]

Type this into an empty query field:

Now when you view the query in datasheet view you should have the results that you're looking for:

Keep in mind that this is a bit of a shortcut, to be proper you really should reference the table along with the field:

whole_name:[example_name].[first_name] & " " & [example_name].[last_name]

You will need to do this if you have more than one table pulled into the query, and you are trying to reference a field that has the same name in both tables. This may seem a bit far-fetched, but it happens more than you would think.