calculation criteria on max(date) (1 Viewer)

[Happy YN]

My query selects all those who donated to a fund.The max on the date restricts all but their last donation, working fine.But now I want to list all those who have not donated anything in the last x days (user defined). I want to use Date()- dateField>30 but now it does not let me peform the query.
Any way I can do this in just one query? Thanks!

SELECT main.dgTitle, main.dgInit, main.dgSurname, main.dgNo, main.dgStreet, main.dgShul, Max(main.dgDate) AS maxdate
FROM main
WHERE (((Date()-[main].[dgdate])>30))
GROUP BY main.dgTitle, main.dgInit, main.dgSurname, main.dgNo, main.dgStreet, main.dgShul;

 

[namliam]

SELECT main.dgTitle, main.dgInit, main.dgSurname, main.dgNo, main.dgStreet, main.dgShul, Max(main.dgDate) AS maxdate
FROM main
GROUP BY main.dgTitle, main.dgInit, main.dgSurname, main.dgNo, main.dgStreet, main.dgShul
HAVING Date()-max([main].[dgdate])>30
;

But it doesnt do what you think it does....

 

[Happy YN]

Thanks but what do you mean

But it doesnt do what you think it does....

 

[namliam]

Well you are getting the max date for your "group by" fields.

I do not know what the content of the fields is, but If your "group by" is not "proper" then you are not fetching only the last payment date....

But if you are sure.... that it is ok... then I am sure it is...

 

[Happy YN]

I'm grouping by people who have donated money and am looking to see who has not paid anything during the last x days. This Sql seems to work fine for me!
Thanks for your help