Report Field Formatting Help needed

[spow]

I have a report that gets it's data from a UNION query that I had to write using SQL statments. My only problem is, when that UNION query is run, for some reason, the fields that I have the format type set to currency loose all their formatting in that query. So when the Report is compiled from that Query, I get unformatted numbers instead of currency. Right clicking in design mode and changing the format field to "$#" doesn't change anything. Do I have to set the formatting in the SQL statments in the UNION query?

I even tried creating another query to select fields from the UNION query and set those formats, but the format pull down menu is blank, setting those to "$#" also doesn't work. What am I doing wrong?

 

[tomschafer]

The changes in this SQL from the SQL in post thread701-181870 are highlighted in red.

SELECT
Table1.newproj_name,
format(Table1.newproj_cost, "Currency&quot as newproj_cost,
null as contproj_cost,
Table3.savings
FROM
Table1 INNER JOIN Table3 ON Table1.newproj_name = Table3.name;

UNION ALL

SELECT
Table2.contproj_name,
null,
format(Table2.contproj_cost, "Currency&quot,
Table3.savings
FROM Table2 INNER JOIN Table3 ON Table2.contproj_name = Table3.name;

The reason you were losing formatting is the fill in column that is null, which is why you do not have to format the Table3.savings column- it is the same in both parts of the union query. As always Good Luck!