Summing fields in Union Query 1

[dmarsh16946]

I'm reporting a series of amounts using a union query like this

SELECT Area, Explorer,Companion,Date,Description,Format(HolidayAmount,'Currency') As HAmount,Format(ClubAmount,'Currency') as CAmount
FROM qryClubExpenses

UNION SELECT Area, Explorer,Companion,Date,Description,Format(HolidayAmount,'Currency') As HAmount,Format(ClubAmount,'Currency') as CAmount
FROM qryLeisureExpenses;

The Format functions show the individual data correctly as currency.

If I now try to do a simple sum of these amounts within the report groupings I get a Data Mismatch error. How can this be fixed?

 

[sxschech]

What about applying the format to the textbox on the report itself rather than formatting in the query? You could also try val(ClubAmount) in your query and then format the textbox on the report.

 

[dmarsh16946]

Thanks sxschech

It works a treat to use Val in the union query then format the total as currency.

So SQL becomes

SELECT Area, Explorer,Companion,Date,Description,Val(HolidayAmount) As HAmount,Val(ClubAmount) As CAmount
FROM qryClubExpenses

UNION SELECT Area, Explorer,Companion,Date,Description,Val(HolidayAmount) As HAmount,Val(ClubAmount) As CAmount
FROM qryLeisureExpenses;