Mail us
Home » MS Access » Microsoft Access Query Formulas Combining Fields, Calculation

Microsoft Access Query Formulas Combining Fields, Calculation

Microsoft Access Query Formulas Combining Fields, Calculation

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!

Claim your free eBook NOW

Enjoy ?

Kind regards,

Ben Beitler-“Your Access Database Expert!

Tags: , , , , , , , ,

3 Responses so far.

  1. best electric shavers says:

    Thanks for that awesome posting. It saved MUCH time 🙂

  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

    • 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?