Dateserial Previous Quarter

[Terry B via AccessMonster.com]

I am trying to show the previous quarters information using the Dateserial
function.
Currently I have a report that shows the Current quarter and I use:
Between DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+1,1)-1 And
DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+4,0)-1
This appears to give the correct results when placed in the criteria for my
query.

How do I modify this to show the Previous quarter?
I check Microsoft and found a bunch of examples using this function but it
did not explain
what each of the parameters actually does.

Thanks

 

[Douglas J. Steele]

DateSerial takes 3 values. Nominally, they're Year, Month and Day in that
order, but they don't have to be valid month or day numbers: for instance,
you can use DateSerial(2005, -1, 35) and you'll get 5 Dec, 2004 (a month of
0 gives you December of the previous year, a month of -1 gives you November
of the previous year, and so on. It then takes the 35th day of November,
which is December 5)

This means that, to get the previous quarter, you need to subtract 3 from
the Month value in both calls to the function:

Between DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3-2,1)-1 And
DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+1,0)-1

 

[Terry B via AccessMonster.com]

Works like a champ!
Thanks for the explanation of how the function works.
It makes more sense now.

Terry

 

[Terry B via AccessMonster.com]

I just noticed something while testing...
The formula you gave seems to exclude the Beginning & Ending days of the
quarter.
It litteraly returned the dates"Between" those two dates.
I changed to:
Between DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3-2,2)-1 And
DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+1,1)-1

It seems to include the Beginning and ending dates now, but Im not sure if Im
over looking something.

Terry

 

[Douglas J. Steele]

You had the -1 in your original post, so I just kept them in, thinking you
had them there for a reason!

DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3-2,2)-1
could actually be
DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3-2,1)
but either way, you should get events that occurred on that day (April 1st,
2005 if you run it today)

DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+1,1)-1
could be
DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+1,0)

As long as the date field to which you're comparing doesn't have times in it
(eg.: you didn't use the Now function to populate it), that should give you
events that occurred on that day (June 30th, 2005 if you run it today)

If you do have times in your date field, use ,1) rather than ,0)

 

[Terry B via AccessMonster.com]

See, I told you I didnt know how this function worked.
You are right...
I had it wrong in the first place.

Thanks again for your input. Everything is working as it should.

Terry