Using SUM function in a UNION query

[GraeagleBill]

I need to bind the Control Source of a text box on the SUM of [Amount] based on the query below. What do I add to the SQL shown below such that I can use the query name as the Control Source of a text box.

SELECT ALL 
[DonRegFam].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]
Where DOE = DOEparms() AND Type = 3

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]
Where DOE = DOEparms() AND Type = 3

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[Funds].[AccountNo],[Funds].[Source],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID]
Where DOE = DOEparms() AND Type = 3;

[pbaldy]

You can never use the query itself in the control source. You could refer to a field in the query if the query was the record source of the form/report. You could use a DSum() on the query in a textbox.

[GraeagleBill]

You can never use the query itself in the control source

Ah yes, of course.............. DUH!

The demands of the app sort of obscured my thinking, but you have awakened me with the use of DSum and the criteria facilities within, problem solved.

=DSum("[Amount]", "FundDonations", "[Type] = 1")  '2,3,...........n, etc

[pbaldy]

Rather than multiple DSum()'s, you might consider a totals query that sums by type and use a subform/subreport based on that to display the results. More dynamic.

[GraeagleBill]

That's essentially what I was trying to do here:

The block shown above is a Sub-Report. Each of the three values "Check", "Cash" and "Direct" were converted per your suggestion to use DSum. The totals were as you suggest with your last post.