Date Evaluation - Max days in a given month?

[Tim]

Can someone tell me a method of returning the total # of days for a given
month, when that month is not the current month necessarily.

I'm trying to filter a date field to a given month that is always 3 months
prior to the current month. So for example, since it is currently July, I
would like my filter to say "Between 4/1/05 and 4/30/05". It's the 30
that's difficult for me. Since of course next month the same filter should
look to 31 (for May), and only to 28 or 29 in February.

Certainly their is already standard methods for this that I'm just missing?

Thanks for any direction!

Tim

 

[Tim]

Well, disregard, in case you'd like to post some better methods. This
worked for me:

Between DateAdd("m",-3,Date())-Format(Date(),"d")+1 And
DateAdd("m",-2,Date())-Format(Date(),"d")

 

[Jeff Boyce]

Tim

Take a look at the DateSerial() function. There's a way you can "trick" it
into determining the number of days in the month without calculating the
number of days in the month. For example (your syntax may vary):

Between DateSerial(Year(Date()),Month(Date())-3,1) And
DateSerial(Year(Date()),Month(Date())-4,0)

The "zero"th day of a month is the last day of the preceding month.

This approach is not tested, and may run into problems over the boundary
between years.

Good luck

Jeff Boyce
<Access MVP>

 

[John Spencer (MVP)]

Pardon me, but I think you meant

Between DateSerial(Year(Date()),Month(Date())-3,1) And
DateSerial(Year(Date()),Month(Date())-2,0)

And DateSerial is pretty smart and will work over month and year boundaries with
no problem. And as I really found out it even (unexpectedly) works out one and
two digit years into the 20th or 21st century. I'm not sure I am totally happy
about that fact. Enter 2 as the year and it gets interpreted as 2002 and I
might really want the 2nd year AD.

 

[Jeff Boyce]

Hmmm? <V8!>

That's what I originally tried, but something isn't computing (here) today.
Thanks, John!

Jeff