Query for quarter end date, depending on current date.

[Gizmodo]

Hello All,

I have been struggling with an issue for quite a while now and wanted the satisfaction of figuring it out myself, yet I have had no luck. So I have come here hoping that what I would like to accomplish is possible. So here it is:



I have a table with dates ranging years back and will continue adding new dated in the future. Basically it is the date a case was opened.

We have 4 quarters at our firm ending respectivley on the following dates 3/31, 6/30, 9/30, and 12/31.

I need an acess query statement that will return dates that are before a specific quarter end date that is closest to the current date. For example, If the current date is 3/28/13 or 4/6/13, I would like the query to return dates before or equal to 3/31/13. Similarly if the current date is 12/30/12 or 1/5/13, I would need the query to return dated before or equal to 12/31/12.

Any help would be appreciated.

Thank you in advance.

[orange]

What exactly is the issue you are having? Logic? Coding?

Some thoughts/facts:

For a given Date(mdy), the quarter must be in the same Year.
The Date must be before or equal the quarter (ending date).
If the Month(YourDate) is 1,2 or 3 , then the quarter is 1
If the Month(YourDate) is 4, 5 or 6 , then the quarter is 2
If the Month(YourDate) is 7, 8 or 9, then the quarter is 3
If the Month(YourDate) is 10, 11 or 12, then the quarter is 4

[Gizmodo]

Im having a coding/logic problem and really do not know if acess is able to preform what I would like done.

The query I would like to design does not pull dates from the current quarter you are in like you had suggested Orange. (just becuase the current date is 2/10/12 doesnt mean I am looking for a query for end of quarter 1.)

Let me break it down:

Quarterly reports are due based on the 4 quarters as described above.(3/31, 6/30, 9/30, 12/31)

At the end of a quarter, lets suppose it is the first quarter with a end date of 3/31/12, a report is due, outlining all cases opened on or before that date within a three year period. Ie (4/1/09-3/31/12).

Another example would be quarterly report for quarter ending in 12/31/12. The period for this would be (1/1/10-12/31/12).

Quarterly reports are at times submitted anywhere from a week prior to or a week after the end of a quarter.

I would like to code the query so that when it is accessed, it should note the current date (Now() or Date() or whatever other code im not coming up with) and based upon that current date which could be up to a week prior or a week after the end of a quarter (3/31/12 for example), generate the dates that are between 4/1/12 and 3/31/12.

I am at a loss as to how this will be coded?

Would it be in the where clause?
Would it be an iff or switch statement?
Is it even possible?

THANK YOU

[orange]

How about giving some sample records? Along with what you want in the output.
Are these due dates, report dates, collect for a quarter end report?

[Gizmodo]

Thanks for fast reply Orange....

As the records are confidential information pertaining to the government I can not supply them but i will provide you now with a mock listing of columns and then i will attempt to make a mock table a bit later.

Table Columns: FileNo, LastName, FirstName, Status, Date Opened

Output: FileNo, LastName, FirstName, Status, DateOpened (WHERE DATEOPENED is between 1/1/10 and 12/31/12, If current date is anywhere from lets say 12/26/12 - 1/7/13)

Hope that helps

[orange]

So if a File 99 was Opened 5/23/11 ( which is between 1/1/10 and 12/31/12), you want the output to be ???? (what date)

[Gizmodo]

the output should remain the same as the input so (5/23/11)

So:
FileNo, LastName, FirstName, Status, Date Opened
99, Doe, Jane, Missing, 5/23/11

The query does nothing but sort the records, new outputs are not formed nor are inputs altered to a new output

[orange]

So where does the nearest quarter fit?

[Gizmodo]

FileNo	LastName	FirstName	Status	DateOpened
1	Doe	Jane	Dead	5/23/2003
2	Mark	Joe	Alive	6/25/2006
3	Smith	John	Missing	9/13/2007
4	Jay	Frank	Captured	4/19/2010
5	Martinez	Bob	Lost	6/20/2010
6	Stone	Sally	Sleeping	8/13/2011
7	Hank	Tim	Driving	9/16/2012
8	Boyd	Robert	Running	12/25/2012
9	Ann	Kerry	Sleeping	3/13/2013
10	Hills	Beverly	Alive	4/13/2013
11	Jones	George	Dead	5/2/2013
12	Dane	Jeffery Dane	Lost	5/9/2013

In this example, If the current date is 1/6/13 or 12/28/12, (between 1/1/10-12/31/12) records 4-8 will be selected

[Gizmodo]

The nearest quarter should infact actualy infact just entitle the following

If current date is between 12/26/(Current Year) and 1/7/(Next year in this case since the year will be 1+)- search for records where date opened between (1/1/(Current Year -2 if current month = 12 OR Current Year -3 if current month i= 1) - 12/31/(Current Year if month = 12 OR previous year if current month =1)

If current date is between 3/26/(Current Year) and 4/7/(Current Year)- search for records where date opened between (4/1/(current year - 3) - 3/31(Current Year))

and so on

[orange]

I just mocked up something to show the logic.
I deals with simulated current dates and some corresponding dates.
The QtrLow and QtrHi are the date ranges used to collect Opened Case records for reporting. Is this right?

Simulated current date   3/27/2012
Select id, qtrDate, qtrLow, qtrHi from tblQtrsWithRanges where Id = 1
quarter    1
quarterEnd 3/31/2012
QtrLo      3/26/2012
QtrHi      4/7/2012
Search  sql will be Select records from cases where Opendate Between #3/26/2009# AND #4/7/2012#;

Simulated current date   3/21/2012
Select id, qtrDate, qtrLow, qtrHi from tblQtrsWithRanges where Id = 1
quarter    1
quarterEnd 3/31/2012
QtrLo      3/26/2012
QtrHi      4/7/2012
Search  sql will be Select records from cases where Opendate Between #3/26/2009# AND #4/7/2012#;

Simulated current date   4/3/2012
Select id, qtrDate, qtrLow, qtrHi from tblQtrsWithRanges where Id = 2
quarter    2
quarterEnd 6/30/2012
QtrLo      6/26/2012
QtrHi      7/7/2012
Search  sql will be Select records from cases where Opendate Between #6/26/2009# AND #7/7/2012#;

Simulated current date   12/29/2012
Select id, qtrDate, qtrLow, qtrHi from tblQtrsWithRanges where Id = 4
quarter    4
quarterEnd 12/31/2012
QtrLo      12/26/2012
QtrHi      1/7/2013
Search  sql will be Select records from cases where Opendate Between #12/26/2010# AND #1/7/2013#;

Simulated current date   7/8/2012
Select id, qtrDate, qtrLow, qtrHi from tblQtrsWithRanges where Id = 3
quarter    3
quarterEnd 9/30/2012
QtrLo      9/26/2012
QtrHi      10/7/2012
Search  sql will be Select records from cases where Opendate Between #9/26/2009# AND #10/7/2012#;

[Gizmodo]

Wow again thanks again for your help and quick response! I think it is almost there, however:
The report due at end of each quarter always bares the same range of dates, they are not dependent on the current date in which the query is accessed.
quarter 1 report range IS ALWAYS (4/1/Year - 3/31/Year) (3 year period)
quarter 2 report range IS ALWAYS (7/1/Year - 6/30/Year) (3 year period)
quarter 3 report range IS ALWAYS (10/1/Year- 9/30/Year) (3 year period)
quarter 4 report range IS ALWAYS (1/1/Year - 12/31/Year) (3 year period)

My previous code had the following logic:
Select (All Columns)
WHERE (((Sheet1.[Date Opened])>=(DateAdd("yyyy",-3,Date())-(DatePart("d",Now()))));

What this does, is give me the records where the date is up to 3 year less than the current date, but the query would only be accurate if i was running it on the day after the end of quarter date.

IE quarter one would need to be accessed exactly on 4/1/. However, the query is sometimes accessed on 4/2 or 3/30 due to holidays, weekends or whatever other reason, which would completely throw off the count

SO MY ISSUE LIEs ON, how can i get the query to run accurately even if the date i access the query is a few days before or a few days after the date.

If possible the query should follow logic such as

if current date is between 3/25 and 4/8
then show records where opendate between (4/1 - 3/31) <--Within a 3 year period ofcourse
if current dated is between 6/25 and 7/8
then show records where opendate between (7-1 - 6/30) <--Within a 3 year period ofcourse

and so on

[orange]

A few points/questions.

1) What did this mean Current Year -2 if current month = 12 in post#10?

2) if current date is between 3/25 and 4/8
then show records where opendate between (4/1 - 3/31) <--Within a 3 year period ofcourse

and what if current date is 3/22 ? If the date is not in that range, what to do?

[orange]

Here are 10 simulated Current dates with the proposed Date/Report and SQL details. Needs confirmation or adjustment,

0************  Simulated current date M/D/Y  3/27/2012
Check for Month-Day  between  3 - 25 to 4 - 8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter    1
quarterEnd 3/31/2012
ReportperiodStart 4/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    3/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report    4/8/2012
quarter    1
Adjusted quarterEnd 3/31/2012
Adjusted ReportperiodStart 4/1/2009
QtrLo      3/25/2012
QtrHi      4/8/2012
Search  sql will be Select records from cases where Opendate Between #4/1/2009# AND #3/31/2012#;

1************  Simulated current date M/D/Y  3/21/2012
Check for Month-Day  between  3 - 25 to 4 - 8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter    1
quarterEnd 3/31/2012
ReportperiodStart 4/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    3/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report    4/8/2012
quarter    1
Adjusted quarterEnd 3/31/2012
Adjusted ReportperiodStart 4/1/2009
QtrLo      3/25/2012
QtrHi      4/8/2012
Search  sql will be Select records from cases where Opendate Between #4/1/2009# AND #3/31/2012#;

2************  Simulated current date M/D/Y  4/3/2013
Check for Month-Day  between  6 - 25 to 7 - 8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter    2
quarterEnd 6/30/2012
ReportperiodStart 7/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    6/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report    7/8/2012
quarter    2
Adjusted quarterEnd 6/30/2013
Adjusted ReportperiodStart 7/1/2010
QtrLo      6/25/2012
QtrHi      7/8/2012
Search  sql will be Select records from cases where Opendate Between #7/1/2010# AND #6/30/2013#;

3************  Simulated current date M/D/Y  12/29/2012
Check for Month-Day  between  12 - 25 to 1 - 8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter    4
quarterEnd 12/31/2012
ReportperiodStart 1/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    12/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report    1/8/2013
quarter    4
Adjusted quarterEnd 12/31/2012
Adjusted ReportperiodStart 1/1/2009
QtrLo      12/25/2012
QtrHi      1/8/2013
Search  sql will be Select records from cases where Opendate Between #1/1/2009# AND #12/31/2012#;

4************  Simulated current date M/D/Y  7/8/2012
Check for Month-Day  between  9 - 25 to 10 - 8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter    3
quarterEnd 9/30/2012
ReportperiodStart 8/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    9/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report    10/8/2012
quarter    3
Adjusted quarterEnd 9/30/2012
Adjusted ReportperiodStart 8/1/2009
QtrLo      9/25/2012
QtrHi      10/8/2012
Search  sql will be Select records from cases where Opendate Between #8/1/2009# AND #9/30/2012#;

5************  Simulated current date M/D/Y  3/27/2011
Check for Month-Day  between  3 - 25 to 4 - 8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter    1
quarterEnd 3/31/2012
ReportperiodStart 4/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    3/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report    4/8/2012
quarter    1
Adjusted quarterEnd 3/31/2011
Adjusted ReportperiodStart 4/1/2008
QtrLo      3/25/2012
QtrHi      4/8/2012
Search  sql will be Select records from cases where Opendate Between #4/1/2008# AND #3/31/2011#;

6************  Simulated current date M/D/Y  3/21/2010
Check for Month-Day  between  3 - 25 to 4 - 8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter    1
quarterEnd 3/31/2012
ReportperiodStart 4/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    3/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report    4/8/2012
quarter    1
Adjusted quarterEnd 3/31/2010
Adjusted ReportperiodStart 4/1/2007
QtrLo      3/25/2012
QtrHi      4/8/2012
Search  sql will be Select records from cases where Opendate Between #4/1/2007# AND #3/31/2010#;

7************  Simulated current date M/D/Y  6/26/2011
Check for Month-Day  between  6 - 25 to 7 - 8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter    2
quarterEnd 6/30/2012
ReportperiodStart 7/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    6/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report    7/8/2012
quarter    2
Adjusted quarterEnd 6/30/2011
Adjusted ReportperiodStart 7/1/2008
QtrLo      6/25/2012
QtrHi      7/8/2012
Search  sql will be Select records from cases where Opendate Between #7/1/2008# AND #6/30/2011#;

8************  Simulated current date M/D/Y  12/29/2011
Check for Month-Day  between  12 - 25 to 1 - 8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter    4
quarterEnd 12/31/2012
ReportperiodStart 1/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    12/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report    1/8/2013
quarter    4
Adjusted quarterEnd 12/31/2011
Adjusted ReportperiodStart 1/1/2008
QtrLo      12/25/2012
QtrHi      1/8/2013
Search  sql will be Select records from cases where Opendate Between #1/1/2008# AND #12/31/2011#;

9************  Simulated current date M/D/Y  9/8/2012
Check for Month-Day  between  9 - 25 to 10 - 8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter    3
quarterEnd 9/30/2012
ReportperiodStart 8/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    9/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report    10/8/2012
quarter    3
Adjusted quarterEnd 9/30/2012
Adjusted ReportperiodStart 8/1/2009
QtrLo      9/25/2012
QtrHi      10/8/2012
Search  sql will be Select records from cases where Opendate Between #8/1/2009# AND #9/30/2012#;

[Gizmodo]

1) What did this mean Current Year -2 if current month = 12 in post#10?

What i meant was if you pull up the 4th quarter report in the month of December( month =12) with the standard adjustment of -3 years from current year... you will get an output much like your simulation, which is incorrect, note below how the period is from 1/1/08-12/31/11, that is infact a 4 year period, not a 3 year period, the correct period should be (11/09-12/31/11). Thus, if you are accessing the 4th quarter report in December where current date is 12/??/????, you would need to subtract 2 year from the current year instead of the standard 3.


8************ Simulated current date M/D/Y 12/29/2011Check for Month-Day between 12 - 25 to 1 - 8 OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??quarter 4quarterEnd 12/31/2012ReportperiodStart 1/1/2012QtrLo M/D before QtrEnd to include in Qtr Report 12/25/2012QtrHi M/D after QtrEnd to include in Qtr Report 1/8/2013quarter 4Adjusted quarterEnd 12/31/2011Adjusted ReportperiodStart 1/1/2008QtrLo 12/25/2012QtrHi 1/8/2013Search sql will be Select records from cases where Opendate Between #1/1/2008# AND #12/31/2011#