Formatting the total column of a crosstab

[keith2511]

I want to format the total column of a crosstab query as currency. The other columns have formatted as currency but not the total.

TRANSFORM Format(Sum([Temp_Import].VAT_Amount),"Currency") AS SumOfVAT_AmountSELECT [Temp_Import].Caterory_Final, Sum([Temp_Import].VAT_Amount) AS [Total Of VAT_Amount]
FROM Temp_Import
GROUP BY [Temp_Import].Caterory_Final
PIVOT Format([Invoice_Date],"yyyy-mm");

Grateful for advice.

Thanks

[June7]

Just set the Format property for the field in query design view. Did you do that for the other field to set its format?

[keith2511]

Hi June,

I couldn't get the FORMAT function to work in design view, so I edited the SQL. I've just tried to format the Total column in design view but no matter how I edit the Total column, I get an error such as

[June7]

Interesting, my suggestion worked for me.

[isladogs]

I couldn't get the FORMAT function to work in design view, so I edited the SQL.

Your screenshot shows a field name [ VAT_Amount] with a space at the start. No such field exists hence the error