Union query - format issue (1 Viewer)

[Tskutnik]

All, Thanks in advance. I'm stuck.
The union query below has 5 fields and 4 underlying sources. Problem is - not all sources have all the same data, and when I plug NULL it changes the formats of all the output to text. All should be number formats.

Unfortunately I cannot plug "0" instead of Null, since 0 in this case is a real value that I don't want to add.

The first [MergeData_Fake] table has the Data Type set to numbers and contains numbers in the table. (I though the first source referenced drove the formats for the output...)

How do I get the Query to produce number formats for each column?

Thanks,
Tony

SELECT
[Portfolio ID],
[YYYYMM],
MV_BOP,
MV_EOP,
Accrual
FROM MergeData_Fake;

Union All Select
[Portfolio ID],
[YYYYMM],
"",
MV_EOP,
""
From MarketValueOnly_Grouped

Union All select
[Portfolio ID],
[YYYYMM],
MV_BOP,
"",
""
From MarketValueOnly_Grouped_BOP

Union All select
[Portfolio ID],
[YYYYMM],
"",
"",
Sumofvalue as Accrual
From AccrualOnly_Grouped

 

[pbaldy]

You're not adding Null, you're adding a zero length string. Try the word Null.