Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

format Union query with dynamic columns 1

Status
Not open for further replies.

sub5

Programmer
Oct 12, 2005
104
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;
 
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
 
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]

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top