Microsoft Access Query Formulas Combining Fields, Calculation

Queries are the heart of any Access database and this 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.

This entry was posted in MS Access, Queries, Utilities and tagged , , , , , , , , . Bookmark the permalink.

3 Responses to Microsoft Access Query Formulas Combining Fields, Calculation

  1. Ben says:

    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?

  2. Tony Bromirski says:

    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

  3. Thanks for that awesome posting. It saved MUCH time :-)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>