Fix Access UNION ALL Query Returning Incorrect Unicode Character
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.

- 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.
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.
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.
Open your Microsoft Access database, navigate to your target query, right-click its tab, and select 'SQL View'.
Find the section of your SQL code where you are combining tables or queries using the 'UNION ALL' operator.
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.
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.

Apply Explicit Type-Conversion Functions
Force Access to recognize the intended data type by wrapping your query expressions in explicit type-conversion functions.
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. Visit the WPS Office Website: Go to the official WPS Office website to find the free installer for your operating system.
- 2. Download the Installer: Click the 'Free Download' button to download the lightweight installation package.
- 3. Install and Start Using: Open the downloaded file, follow the quick installation prompts, and start viewing or editing your Office files immediately.

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.




