Others

How to Concatenate Access Fields Correctly When Values Are Null

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to construct an expression to correctly concatenate string fields in Microsoft Access without producing unexpected results or formatting issues when some of the field values are Null.

Product: Microsoft Access
Device & OS: not provided
Scenario: Writing concatenation expressions (like joining first name, spouse name, and last name) where intermediate fields might be Null rather than simply empty.
Observed behavior: Direct string comparison or concatenation behaves inconsistently because Access treats Null values differently from zero-length (empty) strings.

Before you start

Ensure you know which fields in your Access table are allowed to be Null, and understand the difference between a Null value (no data entered) and a zero-length string (an explicitly blank text value).

Solution 1Recommended

Use the Nz() Function in Expressions

The Nz() function safely converts Null values to zero-length strings, allowing conditional logic and concatenation to work predictably.

In Microsoft Access, a Null value represents missing or unknown data. If you use standard comparison operators (like <> "") directly on a Null value, the result is not guaranteed to behave the same as a zero-length string. Wrapping the field in the Nz() function ensures that if a Null is encountered, it is treated as a standard empty string.

Open Query Design

Open your Microsoft Access database, navigate to the Queries section, and open your target query in Design View.

Enter the Nz expression

In a blank column's Field row, enter your concatenation expression using IIf and Nz. For example: Donor: IIf(Nz([Spouse],"")<>"",[FirstName] & " " & [Spouse] & " " & [LastName],[FirstName] & " " & [LastName])

Run and verify

Click the 'Run' button on the Ribbon. Check the results to ensure that records with Null Spouse fields are concatenated properly without leaving extra blank spaces.

Understanding the Nz Function: The Nz() function takes two arguments: the field to check, and the value to return if it is Null. Here, Nz([Spouse], "") actively prevents the query from failing when it encounters a blank record.

Solution 2

Create a Custom VBA Concatenation Function

For databases with complex fields or multiple optional parameters, writing a custom VBA module offers a cleaner and more scalable approach.

Free Microsoft Office alternative

Manage Data Easily with WPS Office

While Microsoft Access is a powerful tool for complex relational databases, many data tracking, concatenation, and sorting tasks are easier to manage in a spreadsheet environment. WPS Office provides a lightweight, highly compatible alternative to Microsoft Office, featuring a powerful spreadsheet application equipped with modern text functions.

1. Download the software: Visit the official WPS website and download the free WPS Office installation package.
2. Install and launch: Run the installer, follow the on-screen prompts, and launch WPS Spreadsheet.
3. Use TEXTJOIN for easy concatenation: Use the TEXTJOIN function to combine data. By setting the 'ignore_empty' argument to TRUE, WPS Spreadsheet automatically bypasses empty cells without complex IF statements.

100% compatibility with Microsoft Excel (.xlsx) file formats.Advanced text functions like TEXTJOIN automatically ignore blank and Null cells during concatenation.Completely free core features, requiring fewer system resources than standard Microsoft Office installations.Familiar user interface makes transitioning your data processing tasks seamless.

microsoft office alternative - wps office

Frequently Asked Questions

Why does concatenating a Null value with a string return a Null result?

In Microsoft Access, if you use the standard '+' operator for concatenation, a Null value will propagate, turning the entire resulting string into Null. Using the '&' operator is safer because it generally treats Null as an empty string, though conditional statements still require explicit Null handling.

What is the difference between Null and a zero-length string in Access?

A Null value indicates that the data is completely unknown or missing, while a zero-length string ("") indicates that the field is known to be blank. Access treats these two states differently in mathematical expressions and comparisons.

Can I use the IsNull() function instead of Nz()?

Yes, you can use IsNull() within an IIf statement (e.g., IIf(IsNull([Spouse]), ...)), but Nz() is often preferred because it is more concise. Nz() directly checks for Null and substitutes it with a zero-length string or another specified value in a single step.

Office Quick Solutions