Currency Formatting (1 Viewer)

[Bechert]

Hello group,
I have a main report with 5 sub reports. There is a detail page for each company and a totals page at the end. The record source for each sub report is a Union query (combines the detailed information with the total information.

One of the sub reports displays currency amounts. The detailed reports display the currency correctly: $26,001 (no cents).
The totals sub report does not display the currency formatting: 468934

When I run just the totals query the amounts display correctly ($468,934) by using the CCUR(TotalAmount) variable type conversion. The VarType for the amount field in the totals query is 5 (double precision).

When I combine the detail query and totals query into a Union query the detail amounts display correctly but the total amount is missing the formatting.

Here is the union query.

SELECT TblCompany.TblCompanykey, FormatCurrency(ProviderCostsRetrieval([TblCompanykey],1),0) AS TotalCost
FROM TblCompany
ORDER BY TblCompany.TblCompanykey
UNION ALL SELECT 9999 AS TblCompanykey, CCur(Sum(([QryRptProviderCostsDuringPeriod.TotalCost]))) AS TotalCost
FROM QryRptProviderCostsDuringPeriod
GROUP BY 9999;

Thanks for your help.
Bill

 

[CJ_London]

currency is just a number. formatting it changes is from being numeric to text. the first part of your query returns a text value, the second a number.

Personally I would not format fields until they appear on a form or report, in which case you can use the control format which only affects the display.

you can't see it in a union query because the query grid is not available, but taking a select query for example, if you right click on a column, you can format that column to what you want and that is what will display in the query. but use that query as a recordsource to a form or report, the formatting is not preserved and has to be set on the control

 

[MarkK]

I believe that in a UNION query, the format and field names--and so on--of the first SELECT clause are extended to the entire query. Also note that the VBA.FormatCurrency() function actually returns a string. For your query I would try . . .

SELECT TblCompanykey, CCur(ProviderCostsRetrieval(TblCompanykey,1)) AS TotalCost
FROM TblCompany
ORDER BY TblCompanykey
UNION ALL 
SELECT 9999, Sum(TotalCost)
FROM QryRptProviderCostsDuringPeriod
GROUP BY 9999;

. . . and if this is a function you control: ProviderCostsRetrieval(), just get it to return a currency value.