Where clause used on alias field in Union Query

[Trusts]

Hi,

I have a report that is built on a Union Query:

SELECT Contributors.FirstName, Contributors.LastName, Donations.DonationDate, Donations.DonationAmount as "Amount", Donations.Loan, Donations.DonationReported, "Donation" AS Type
FROM Contributors INNER JOIN Donations ON Contributors.ID = Donations.ContributorID
Where Donations.DonationAmount>0
UNION SELECT Contributors.FirstName, Contributors.LastName, Donations.DonationDate, Donations.DonationInKindValue as "Amount", Donations.Loan, Donations.DonationReported, "In Kind" AS Type
FROM Contributors INNER JOIN Donations ON Contributors.ID = Donations.ContributorID
Where Donations.DonationInKindValue>0;


I am sending a where clause to limit the returned records. The where is against "Amount", so I have this:

z = 50
DoCmd.OpenReport "rptDonations", acViewPreview, , "Amount>" & z

What happens is a box pops open asking for the value of Amount. That's one problem, and then when I put in an amount it has no effect on the query. All records are returned.

Ideas?

Thanks,
K

 

[Trusts]

Another option is to send the number in OpenArgs, and then apply it as a filter. Couldn't get that to work either.

 

[dhookom]

Try remove the double-quotes and the aliases in the second select:

SELECT Contributors.FirstName, Contributors.LastName, Donations.DonationDate, Donations.DonationAmount as Amount, Donations.Loan, Donations.DonationReported, "Donation" AS Type
FROM Contributors INNER JOIN Donations ON Contributors.ID = Donations.ContributorID
Where Donations.DonationAmount>0
UNION SELECT Contributors.FirstName, Contributors.LastName, Donations.DonationDate, Donations.DonationInKindValue, Donations.Loan, Donations.DonationReported, "In Kind"
FROM Contributors INNER JOIN Donations ON Contributors.ID = Donations.ContributorID
Where Donations.DonationInKindValue>0;

Duane
Hook'D on Access
MS Access MVP