format Union query with dynamic columns 1

[sub5]

Hello All,

I have a union query based on two crosstab queries. I have formated both xtabs' fields The Value and Total as Standard with 0 decimal places. However in the union all the decimals come back. How do I set the formating in the union to 0 decimals (Please note the number and titles of the union columns can change depending on the records in Project)?

???I have seen postings here which say use format(Total,0) but I don't know how I can use that because my columns are dynamic.???

crosstab SQL

TRANSFORM Sum(qry02_05_CashReportCheck.[Converted to £]) AS [The Value]
SELECT qry02_05_CashReportCheck.RRCAELAllocation, Sum(qry02_05_CashReportCheck.[Converted to £]) AS Total
FROM qry02_05_CashReportCheck INNER JOIN tpcsWeek ON (qry02_05_CashReportCheck.Week = tpcsWeek.Week) AND (qry02_05_CashReportCheck.AP = tpcsWeek.AP)
GROUP BY qry02_05_CashReportCheck.RRCAELAllocation
PIVOT qry02_05_CashReportCheck.Project;


Union query SQL

SELECT *
FROM qry02_10_CashReportCheck

UNION ALL SELECT *
FROM qry02_20_CashReportCheck;

 

[PHV]

You may try this (typed, untested):
TRANSFORM CLng(Nz(Sum(qry02_05_CashReportCheck.[Converted to £]),0) AS [The Value]


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sub5]

Bingo! worked your usual magic again PHV cheers!
ps didn't know about that Nz function either - v useful.
ps for anyone else reading thread - add another bracket in ie:
TRANSFORM CLng(Nz(Sum(qry02_05_CashReportCheck.[Converted to £]),0)) AS [The Value]