MS Access Union Query Changes Numeric values to Text values

Anigito

New Member
Joined
Jul 16, 2011
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
Dear Community,

My question might be a very simple to answer, since I haven't found related threads in the forum. I would really appreciate your support.

What I have:
2019 MS Office
Excel files with the same formatting used as linked tables

What I try to achieve:
Union necessary data for later processing via Access/Excel

What has been done:
Union query which looks like this:
SQL:
SELECT 
KA, 
[MODEL#SUFFIX] AS MODEL, 
GDMI_Types.rTYPE as MEASURE, 
VALUE, 
YEAR as YYYY, 
MONTH as MM, 
WEEK as WW

FROM GDMI_2019_Q LEFT JOIN GDMI_Types ON GDMI_2019_Q.MEASURE = GDMI_Types.GDMI_TYPE

UNION ALL SELECT 
KA, 
[MODEL#SUFFIX] AS MODEL, 
GDMI_Types.rTYPE as MEASURE, 
VALUE, 
YEAR as YYYY, 
MONTH as MM, 
WEEK as WW

FROM GDMI_2020_Q LEFT JOIN GDMI_Types ON GDMI_2020_Q.MEASURE = GDMI_Types.GDMI_TYPE;

Error:
Formatting for VALUE, YYYY, MM, WW should be number (as it is in the source file).
The query has been working well until recently, what exactly caused the issue, I cannot seem to find.
But the result of the query turns these values into text, disrupting further processing and reporting.

Question:
How to fix this format change?
I have tried to use VAL() function for numeric values, but it did not work.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top