How to Make an Access UNION Query Return Zero Values Instead of Blank
Question details
The user needs a way to force a UNION query to return a record with zero values so that the connected Access report does not display entirely blank when no actual records match.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Generating a report based on a UNION query that occasionally returns no matching data.
- Observed behavior
- The Access report appears completely blank and loses its formatting when the underlying UNION queries find no matching records.
Ensure you have a backup of your Access database and switch your query to SQL View to safely modify the code.
Append a Dummy Query with Zero Values to the UNION Statement
By adding an extra query that returns static zero values, you guarantee the report always receives at least one record to display its structure.
Microsoft Access reports require at least one row of data to render the Details section properly. When a UNION query returns no records, the report goes entirely blank.
To bypass this, you can attach an artificial query at the end of your UNION statement. This dummy query explicitly returns zeros or empty strings. Because it is always executed, the report is guaranteed to receive one row of default values.
In the Microsoft Access Navigation Pane, right-click your existing UNION query and select 'Design View'. If it opens in another view, switch to 'SQL View' using the View button on the Home tab.
Write a new SELECT statement that matches the exact number of fields and data types as your existing queries. Use '0' for numeric fields, '""' (empty quotes) for text fields, and 'Null' for date fields. For example: SELECT 0 AS Field1, "" AS Field2, 0 AS Field3.
Add the 'UNION' keyword at the very end of your current SQL code, followed immediately by your newly formulated dummy SELECT statement.
Click the 'Run' button on the Design tab to execute the query. Verify that a row with zero values appears. Open your connected report to confirm it now displays its formatting instead of a blank screen.
Need a Lightweight Alternative for Word, Excel, and PowerPoint?
While Microsoft Access is used for complex databases, WPS Office is the perfect free solution for your everyday document, spreadsheet, and presentation needs. It is highly compatible with Microsoft Office formats, incredibly lightweight, and completely free.
- 1. Download the Installer: Visit the official WPS Office website to download the free software installation package.
- 2. Install WPS Office: Run the downloaded installer file and follow the quick on-screen setup instructions.
- 3. Open and Edit Seamlessly: Launch WPS Office and instantly open your existing Microsoft Word, Excel, and PowerPoint files without formatting loss.
Frequently Asked Questions
Why does an Access report go completely blank without data?
If the Record Source (like a UNION query) returns zero records, the Details section of the report has no data to display. This can cause the entire report structure, including headers and footers, to collapse or appear as a blank white page.
Will adding a zero-value dummy query affect my report totals?
If you are summing values, adding a zero will not change the mathematical total. However, if you are counting records (Count) or calculating averages (Avg), the extra dummy row will skew the results. You will need to filter out the zero values in your report's calculation expressions.
Can I use 'UNION ALL' instead of 'UNION' for the dummy query?
Yes. 'UNION ALL' is generally faster because it skips the internal process of checking for duplicate records. If your dummy query uses default zeros and you are not concerned about duplicate zero rows, appending it with 'UNION ALL' is highly recommended.
What if my query includes Date/Time fields?
For Date/Time fields, you cannot use a numeric '0'. You must use a static default date like '#1/1/1900#' or use 'Null' in your dummy SELECT statement to ensure the data types match the preceding queries in the UNION statement perfectly.
