Queries are the heart of any Access database and ms access query is typically utilised especially when wanting to calculate.
A common type of calculation is known as ‘Concatenate’ which is demonstrated here with the ‘FirstName’ and ‘LastName’ fields.
Also, this query uses the common ‘nz’ function to deal with null to zero calculations.
Note the naming conventions for fields i.e. no spaces but spaces are acceptable for aliases.
As a Thank You for Visiting!
Enjoy ?
Kind regards,
Ben Beitler-“Your Access Database Expert!
Tags: Access functions, calculate in a query, calculating queries, Concatenate in a query, Expression Builder, Formula query, nz function in Access, query formulas, text calculations in a query
Thanks for that awesome posting. It saved MUCH time 🙂
A couple of quick questions about concatenation in a query (2010) and displaying results on a form. I want to do the following things with my data:
1. Concatenate up to 5 data fields (as individual parts of an mailing address)
2. format it with carriage returns so the address looks like a postal address
3. Allow it to show up in an access 2010 form in a single field (without
4. Avoid the extra carriage returns when [Address2] is null
Sample Addresses:
Format 1
———-
123 E MAIN ST
CITY, ST 12345
Format 2
———-
123 E MAIN ST
Apt 2, 2nd Floor
CITY, ST 12345
1. Concatenate up to 5 data fields: [Field1] & Chr(13) & Chr(10) & [Field2] & Chr(13) & …. Where The Chr function = carriage return + new line.
2. Format? If you mean upper and lower cases you can use the UCase and LCase functions wrapped around the field. Otherwise formats are carried out in the design for a form or report.
3. Single field: is simply using the field concatenate operator (&) but just add a literal “,” character in between.
4. To handle null values (or empty strings) you can wrap and test each field in a nested IIF function before concatenating fields. IsNull Function returns true or false which is tested as the condition inside the IIF function.
Ideally, writing a VBA function to handle the above workflow may be the alternative approach but will require some VBA knowledge – Do you need help with this?
Thanks