Query request Diff information in a popup

[soulice]

Here is the query....the Diff column is the difference between a field and a calculated field. Why do I get a popup asking to fill out the Diff value. If I leave it blank and click ok it runs and is correct.

SELECT DonationRecipients.RecipientName, DonationRecipients.ApprovedTaxCreditAmount, Sum(CCur(GetTaxPercentage([Donations].[DonationDate],[Donations].[DonationValue]))) AS TaxCredit, [ApprovedTaxCreditAmount]-[TaxCredit] AS Diff
FROM DonationRecipients INNER JOIN Donations ON DonationRecipients.RecipientID = Donations.RecipientID
GROUP BY DonationRecipients.RecipientName, DonationRecipients.ApprovedTaxCreditAmount, Donations.RecipientID, Diff;

 

[MarkK]

I don't think you can GROUP BY Diff, since it doesn't appear in any of the source queries. GROUP BY [ApprovedTaxCreditAmount]-[TaxCredit] instead...

SELECT DonationRecipients.RecipientName, DonationRecipients.ApprovedTaxCreditAmount, Sum(CCur(GetTaxPercentage([Donations].[DonationDate],[Donations].[DonationValue]))) AS TaxCredit, [ApprovedTaxCreditAmount]-[TaxCredit] AS Diff
FROM DonationRecipients INNER JOIN Donations ON DonationRecipients.RecipientID = Donations.RecipientID
GROUP BY DonationRecipients.RecipientName, DonationRecipients.ApprovedTaxCreditAmount, Donations.RecipientID, [ApprovedTaxCreditAmount]-[TaxCredit];

 

[soulice]

I put Diff in the group by replacing [ApprovedTaxCreditAmount]-[TaxCredit] as an attempt to fix the popup issue...to no avail.

 

[MarkK]

If you post a database I'll take a look at it. SQL query text melts my cheese so fast it isn't funny. I like to play with them in the design grid and just keep kicking at them till they work.
Cheers,
Mark

 

[soulice]

Your comment about kicking it around in the designer got me thinking. Changed the GroupBy to Expression and voila....went away.

 

[MarkK]

Good for you!

Yeah, with queries I rarely try to work with the SQL text directly, and even queries with sub-queries: I'll write the sub-query in the grid and copy the SQL into my main query.

Mark