Others

Fix Access UNION ALL Query Returning Incorrect Unicode Character

Muhammad TalhaMuhammad Talha Nov 3, 2026 869 views

Question details

A Microsoft Access UNION ALL query returns an unexpected Unicode character instead of a number when combining a numeric field with a NULL value.

How to Fix Access UNION ALL Query Returning Incorrect Unicode Characters
Product
Microsoft Access
Device & OS
not provided
Scenario
Executing a UNION ALL SQL query that combines data from a numeric field and a NULL expression.
Observed behavior
Access incorrectly infers the data type of the combined column and outputs a Unicode character instead of the expected numeric value. The result may fluctuate depending on the order of the SELECT statements.
Before you start

Ensure you have safely backed up your Access database before modifying SQL queries, and verify the original data types of the fields in your source tables.

Solution 1Recommended

Place the Numeric Field Before the NULL Expression

Reorder your query so that Access can correctly infer the data type based on the first SELECT statement in the UNION query.

Microsoft Access determines the data type of a UNION column based on the first SELECT statement it encounters. If the first statement contains a NULL expression, it may default to a text format, causing subsequent numeric data to be misinterpreted as Unicode characters.

1
Open the query in SQL View

Open your Microsoft Access database, navigate to your target query, right-click its tab, and select 'SQL View'.

2
Locate the UNION ALL structure

Find the section of your SQL code where you are combining tables or queries using the 'UNION ALL' operator.

3
Reorder the SELECT statements

Ensure the query pulling the actual numeric table field comes first. Format it similar to: SELECT A FROM TableA UNION ALL SELECT NULL AS A FROM TableA.

4
Execute and verify

Click 'Run' on the Query Design ribbon to execute the query and confirm that the column now returns expected numeric values instead of Unicode characters.

Place the Numeric Field Before the NULL Expression
Correct Data Inference: By placing the numeric field first, Access successfully locks the column type to numeric, avoiding any character encoding mismatch.
Free Microsoft Office alternative

Discover WPS Office for Your Data and Document Needs

While WPS Office does not include a direct database management tool like Microsoft Access, it serves as a highly compatible, free, and lightweight suite for all your daily productivity tasks. Seamlessly handle documents, spreadsheets, and presentations without heavy subscription fees.

  1. 1. Visit the WPS Office Website: Go to the official WPS Office website to find the free installer for your operating system.
  2. 2. Download the Installer: Click the 'Free Download' button to download the lightweight installation package.
  3. 3. Install and Start Using: Open the downloaded file, follow the quick installation prompts, and start viewing or editing your Office files immediately.
Highly compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Comprehensive spreadsheet functionalities for advanced data analysis and management.Lightweight installation that runs fast and smoothly on various operating systems.Familiar and intuitive user interface makes transitioning incredibly easy.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access query return Unicode characters instead of numbers?

This commonly occurs in UNION or UNION ALL queries when a NULL expression precedes a numeric field in the query structure. Access attempts to infer the data type from the first expression, which may default to a text format, causing the subsequent numeric data to render as incorrect Unicode text.

Can I fix the Access UNION ALL data type issue without changing the order of my SELECT statements?

Yes, you can fix the issue by using explicit type-conversion functions such as Int(), CLng(), or CDbl() around your fields and NULL values. This forces Access to interpret the entire column as a specific numeric type regardless of the SELECT statement order.

Is this unexpected UNION ALL query behavior a bug in Microsoft Access?

Many database developers consider this data type inference behavior a known quirk or limitation within the Microsoft Access database engine. It strictly relies on the first returned value to define the column's data type, which struggles when resolving mixed NULL and numeric outputs.