date calculations/filtering

[petern]

I need to do a report to followup on new clients at 3 months, 6 months and 1 year. The idea is, if I print the report this month (September, 2010), I will get a list of clients who were seen in June, March and last August. As a test for the 3 month part, I tried playing around with =DateAdd("m",-3,Date()) as an expression in the criteria of the date field in a query, but it isn't returning any results at all.

I suspect this is one of those things that should be simple, but it just isn't there. Is it possible to do it this simply, or do I need to be a lot more specific and use Between And and date serials, etc?

Thanks

[Wendell]

Do you want to see the same set of clients regardless of when in September 2010 you run the report. If that's the case your criteria would need to include anyone seen any day in June 2010, and the criteria for that group would need to be Between #6/1/2010# and #6/30/2010# so you could calculate that as Between DateSerial(Year(DateAdd("m",-3,Date())),Month(DateAdd("m",-3,Date())),1) And DateSerial(Year(DateAdd("m",-2,Date())),Month(DateAdd("m",-2,Date())),0). As you suspected it isn't simple, but it can be done. (I didn't actually test the expression, so you may find a missing parenthesis in it.) Doing the 6 month and 1 year cases would be very similar. Also note that this uses a trick to back up to the last day of the prior month by using 0 as the day value in DateSerial().

[petern]

Thanks Wendell. I was coming to the conclusion that I would have to use this method. I've done it before but your formulas will save me a bit of time. I'm aware of the back-dating trick as I've seen it in many of Hans' posts from previous years on a previous forum.
Beer to you: