To join or not to join, that’s concatenation for you! How do you join or concatenate Access fields together for your queries or other objects (including forms and reports) is a matter of mastering the rules and knowing the operators.
There are plenty of simple demonstrations and video tutorials around on the web showing you how to combine and joins fields from tables in a calculation using the concatenate principle.
Most Access developers and users will use the & (ampersand) operator which will join the fields together – like the glue in between two fields. For example:
Fullname: [First name] & " " & [Surname]
However, have you seen this example?
Fullname: [First name] + " " + [Surname]
The above will provide the same results – or does it?
How To Concatenate Access Fields In Queries – Knowing The Difference
So the difference between using the & (ampersand) and the + (plus sign) will sometimes show different results when you want to concatenate Access fields together. We know the + (plus sign) will calculate numeric values as a sum result but when the fields are of a text data type, they behave the same way (normally that is) as using the & (ampersand) operator gluing two or more fields together.
But here’s one difference you might want to be mindful of and that is what if a field is of a Null value (i.e. no value in a field)? Take a look at the image below which shows the two methods and how they differ.
This can show missing values or results or even cause errors when being called into other calculations with other queries, forms, reports as wells as programming aspects to your database. This is called propagating nulls. Normally you do not want to propagate nulls. You want to see whatever is there right!
By using the conventional & (ampersand) operator as you first choice (and really should be the only choice too), the concatenate Access methodology is sound and protects you from missing values and expressions in your Access database application.
To learn more about how to calculate in your database queries and other aspects of Microsoft Access, take a look at the various eBook offers covering the main modules.
Tags: Access, concatenate access, concatenate in access query, concatenate ms access, how to join text fields, joining strings in access
If you are going to use the ‘+’ sign instead of the ‘&’ operator, you may want to include other Access functions to test for a field’s Null value which could include a more complex calcuation with the like of nesting an IIF with the IsNull functions which are both available in Access.