Enable DateAdd as criteria in aggregate function query?

[MaddiMond]

My dear friends at the tek-tip forum,

I have been trying to create a query that lists records that have one date date2 two month after another date date1. When I entered the following criteria line into the date2 field in my query: DateAdd("m", 2, [date1]) and tried to execute the query, the following message appeared:
You tried to execute a query that doe not include a specified expression date2=DateAdd("m", 2, [date1]) as part of an aggregate function. The fields in my query have group by in the total row and one field as sum, but I could probably remove that because I sum this field in the report footer...Can I change the query somehow to enable the DateAdd part?

Thanks.

Maddi

 

[lespaul]

switch to the SQL view of the query, make sure that all the fields in your SELECT list that aren't aggregated (using the SUM, COUNT, AVG functions) are in the GROUP BY clause:

SELECT Field1, Field2, DateAdd("m", 2, [date1]) As Date2, SUM(SomeOtherField)
From SomeTableName
GROUP BY Field1, Field2, DateAdd("m", 2, [date1])

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual