Date function for "Last Quarter"

[Gordon]

I'm trying to define a function for the last quarter that when
selected will allow me to populate "Start from" and "To" text boxes.
I can make the To bit work but not the From. The code I currently
have is:

Me!txtDateFrom = DateAdd("q", -1, DateSerial(Year(Now()),
DatePart("q", Now()), 1))

As of the date of posting (20 March 2010), this correctly produces 01
October 2009, but if I go back in time by resetting my computer date,
it produces a date of 01 January 2009 (instead of 01 July 2009).

My formula is clearly wrong somewhere but despite many permutations I
just can't get it to work.
Can anyone help me please?

Gordon

 

[Tom van Stiphout]

On Sat, 20 Mar 2010 03:55:28 -0700 (PDT), Gordon <[email protected]>
wrote:

Permuting code is never a good idea. It breeds tinkering which is slow
and produces bad code.
This is how I think about it. First find the date of the beginning of
the current quarter. DateSerial takes a year/month/day (not a
quarternumber as in your example). For Mar 20 2010 the date should be
1/1/2010. DatePart("q",Date) returns a number from 1 to 4. (Note I use
Date rather than Now because I don't need the Time component) How can
we turn that into a month number 1, 4, 7, or 10 for the beginning of
each quarter? That formula can be calculated using high school linear
regression math, but you can probably see the solution:
monthNo = (quarterNo-1)*3+1
So the formula so far is:
DateSerial(Year(Date), (DatePart("q", Date) - 1) * 3 + 1, 1)
To go back one quarter we use the DateAdd function:
DateAdd("q", -1, myDate)
Putting it all together:
DateAdd("q", -1, DateSerial(Year(Date), (DatePart("q", Date) - 1) * 3
+ 1, 1))

-Tom.
Microsoft Access MVP

 

[Gordon]

Permuting code is never a good idea. It breeds tinkering which is slow
and produces bad code.
This is how I think about it. First find the date of the beginning of
the current quarter. DateSerial takes a year/month/day (not a
quarternumber as in your example). For Mar 20 2010 the date should be
1/1/2010. DatePart("q",Date) returns a number from 1 to 4. (Note I use
Date rather than Now because I don't need the Time component) How can
we turn that into a month number 1, 4, 7, or 10 for the beginning of
each quarter? That formula can be calculated using high school linear
regression math, but you can probably see the solution:
monthNo = (quarterNo-1)*3+1
So the formula so far is:
DateSerial(Year(Date), (DatePart("q", Date) - 1) * 3 + 1, 1)
To go back one quarter we use the DateAdd function:
DateAdd("q", -1, myDate)
Putting it all together:
DateAdd("q", -1, DateSerial(Year(Date), (DatePart("q", Date) - 1) * 3
+ 1, 1))

-Tom.
Microsoft Access MVP








- Show quoted text -

Hey Tom,

A million thanks for the solution and a million more for taking the
time to spell out the rationale. Now I just have to work out
expressions for Last Week, Last Month, Last Fiscal Year, Last Calendar
Year )

Gordon