Count all dates within the last year 1

[mbowler9]

Hello all. I have a field in a query that counts all the fields that have a date, regardless of when that date is. It looks like:

sw908scount: Sum(IIf(IsNull([sw908_submitted]),0,1))

Now I would like to count them only if the date in "sw908_submitted" is a year or less old (within the last year). I am sure this is a simple modification, but until now I have not had to work with dates in this manner.

I was thinking something along the lines of:

sw908scount: Sum(IIf(Now()-[sw908_submitted]<=1,IIf(IsNull([sw908_submitted]),0,1),0))

But this is not working for me. It doesn't result in an error, but most of the figures are 0 with a couple of 1s.

Thanks

 

[dhookom]

Try:
sw908scount: Sum(Abs([sw908_submitted]<=DateAdd(&quot;yyyy&quot;,-1,Date()) AND [sw908_submitted]<=Date()))

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mbowler9]

Thanks Duane. Only needed to change <= to >=. I probably didn't explain it correct.

 

[dhookom]

mbowler9,
My bad...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.