Format a Union Query???

[MorningSun]

Is it possible to apply formating to a union query? I have my query working but it applies currency formatting to all of the returned records. Only the records from one portion of the union is currence, the other is standard. I need for it to remain this way. Is it possible?

Here's my SQL. I need items from the first portion to be currency, the second portion to be standard:

SELECT BudgetInfo.BudgetID, BudgetInfo.Year, Group.Group, Group.GroupID, Dept.Dept, BudgetInfo.CC, Dept.Area, CC.Desc, Items.ItemID, Items.Item, JanPlan, JanAct, FebPlan, FebAct, MarchPlan, MarchAct, AprPlan, AprAct, MayPlan, MayAct, JunePlan, JuneAct, JulyPlan, JulyAct, AugPlan, AugAct, SeptPlan, SeptAct, OctPlan, OctAct, NovPlan, NovAct, DecPlan, DecAct
FROM (BudgetInfo LEFT JOIN (CC LEFT JOIN Dept ON CC.DeptID = Dept.DeptID) ON BudgetInfo.CC = CC.CC) LEFT JOIN ((Detail LEFT JOIN Items ON Detail.ItemID = Items.ItemID) LEFT JOIN [Group] ON Items.GroupID = Group.GroupID) ON BudgetInfo.BudgetID = Detail.BudgetID
;UNION SELECT BudgetInfo.BudgetID, BudgetInfo.Year, Group.Group, Group.GroupID, Dept.Dept, BudgetInfo.CC, Dept.Area, CC.Desc, Items.ItemID, Items.Item, JanPlan, JanAct, FebPlan, FebAct,MarchPlan, MarchAct, AprPlan, AprAct, MayPlan, MayAct,JunePlan, JuneAct, JulyPlan, JulyAct, AugPlan, AugAct, SeptPlan, SeptAct, OctPlan, OctAct, NovPlan, NovAct, DecPlan, DecAct
FROM (BudgetInfo LEFT JOIN (CC LEFT JOIN Dept ON CC.DeptID = Dept.DeptID) ON BudgetInfo.CC = CC.CC) LEFT JOIN ((manpower LEFT JOIN Items ON Manpower.ItemID = Items.ItemID) LEFT JOIN [Group] ON Items.GroupID = Group.GroupID) ON BudgetInfo.BudgetID = Manpower.BudgetID
ORDER BY Group.GroupID;

Thanks so much!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[lupins46]

Use the Format() function on the fields you want to format and use Cstr() to convert them to text. You cannot have two different datatypes in one column.

Cstr(format(thisfield,"00.00"))
cstr((format(thatfield,"$##.00")