Quarterly Restriction

  • Thread starter Leviathan via AccessMonster.com
  • Start date
L

Leviathan via AccessMonster.com

I'm trying to edit the following query that restricts a date field to the
current month, to restrict to the current quarter, any ideas?
 
K

KARL DEWEY

In design view add another field to the grid like this ---
Year_QTR: Format([YourDateField], "yyyyq")
In the criteria row put this ---
Format(Date(), "yyyyq")
 
L

Leviathan via AccessMonster.com

Unfortunately, I can't use this consistently because we run this report for
the previous month... so if I run it early January for Oct - Dec., it'd
restrict to Jan. - Mar.

KARL said:
In design view add another field to the grid like this ---
Year_QTR: Format([YourDateField], "yyyyq")
In the criteria row put this ---
Format(Date(), "yyyyq")
I'm trying to edit the following query that restricts a date field to the
current month, to restrict to the current quarter, any ideas?
 
K

KARL DEWEY

Your words were "to restrict to the current quarter" so that is what I did.
If you want the previous 3 months then try this --
Between DateAdd("m",-3, (Date() - Day(Date()))+1 AND (Date() - Day(Date()))

This subtract the current day of month from today resulting in the last dat
of last month. For 3 months prior it adds one day to give the first of month
annd then subtracts 3 months.

--
KARL DEWEY
Build a little - Test a little


Leviathan via AccessMonster.com said:
Unfortunately, I can't use this consistently because we run this report for
the previous month... so if I run it early January for Oct - Dec., it'd
restrict to Jan. - Mar.

KARL said:
In design view add another field to the grid like this ---
Year_QTR: Format([YourDateField], "yyyyq")
In the criteria row put this ---
Format(Date(), "yyyyq")
I'm trying to edit the following query that restricts a date field to the
current month, to restrict to the current quarter, any ideas?

=DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1) And <DateSerial(Year(Date()),Month(Date()),1)
 
L

Leviathan via AccessMonster.com

Sorry for the confusion, thanks.

KARL said:
Your words were "to restrict to the current quarter" so that is what I did.
If you want the previous 3 months then try this --
Between DateAdd("m",-3, (Date() - Day(Date()))+1 AND (Date() - Day(Date()))

This subtract the current day of month from today resulting in the last dat
of last month. For 3 months prior it adds one day to give the first of month
annd then subtracts 3 months.
Unfortunately, I can't use this consistently because we run this report for
the previous month... so if I run it early January for Oct - Dec., it'd
[quoted text clipped - 9 lines]
 
L

Leviathan via AccessMonster.com

One other issue...

The query below works if it's the beginning of a new quarter, but I would
also need to calculate for current quarter if it is not the beginning of a
new quarter.

In other words, if I run in December... I'd need the restriction to be for
10/1/2007 - 11/30/2007, If I run in January, I'd need the restriction to be
for 10/1/2007 - 12/31/2007. Right now, if I run the query below it runs for
9/1/2007 - 11/30/2007.

Any ideas?
Sorry for the confusion, thanks.
Your words were "to restrict to the current quarter" so that is what I did.
If you want the previous 3 months then try this --
[quoted text clipped - 9 lines]
 
K

KARL DEWEY

What if it is February? What time span do you want then?
I gave you both ways separately.

So use an IIF statement to select which to use. If it is the first month of
the quarter use one and if the last month of the quarter use the other.

Do not know what you want for the mid quarter month.
--
KARL DEWEY
Build a little - Test a little


Leviathan via AccessMonster.com said:
One other issue...

The query below works if it's the beginning of a new quarter, but I would
also need to calculate for current quarter if it is not the beginning of a
new quarter.

In other words, if I run in December... I'd need the restriction to be for
10/1/2007 - 11/30/2007, If I run in January, I'd need the restriction to be
for 10/1/2007 - 12/31/2007. Right now, if I run the query below it runs for
9/1/2007 - 11/30/2007.

Any ideas?
Sorry for the confusion, thanks.
Your words were "to restrict to the current quarter" so that is what I did.
If you want the previous 3 months then try this --
[quoted text clipped - 9 lines]
=DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1) And <DateSerial(Year(Date()),Month(Date()),1)
 
L

Leviathan via AccessMonster.com

If it was February... it'd restrict the quarter to 1/1/2007 - 2/1/2007.
Basically, the reports we run are always for the previous month... so we'd
want the quarter that the previous month fell into.

KARL said:
What if it is February? What time span do you want then?
I gave you both ways separately.

So use an IIF statement to select which to use. If it is the first month of
the quarter use one and if the last month of the quarter use the other.

Do not know what you want for the mid quarter month.
One other issue...
[quoted text clipped - 16 lines]
 
K

KARL DEWEY

So for your IIF statement use this ---
IIF((Format([YourDateField],"m") Mod 3) =1, Last_Quarter, Current_Quarter)
--
KARL DEWEY
Build a little - Test a little


Leviathan via AccessMonster.com said:
If it was February... it'd restrict the quarter to 1/1/2007 - 2/1/2007.
Basically, the reports we run are always for the previous month... so we'd
want the quarter that the previous month fell into.

KARL said:
What if it is February? What time span do you want then?
I gave you both ways separately.

So use an IIF statement to select which to use. If it is the first month of
the quarter use one and if the last month of the quarter use the other.

Do not know what you want for the mid quarter month.
One other issue...
[quoted text clipped - 16 lines]
=DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1) And <DateSerial(Year(Date()),Month(Date()),1)
 
L

Leviathan via AccessMonster.com

Karl,

Thanks for your help. One quick question, since 2 MOD 3 = 1; the IIF
statement would have to be IIf((Format([PAID_DATE],"m") Mod 3)=1 And Format(
[PAID_DATE],"m")<>"2","Last_Quarter","Current_Quarter"), right?

KARL said:
So for your IIF statement use this ---
IIF((Format([YourDateField],"m") Mod 3) =1, Last_Quarter, Current_Quarter)
If it was February... it'd restrict the quarter to 1/1/2007 - 2/1/2007.
Basically, the reports we run are always for the previous month... so we'd
[quoted text clipped - 12 lines]
 
K

KARL DEWEY

No.
Month Mod 3 Display quarter
1 1 Last quarter
2 2 Current quarter
3 0 Current quarter
4 1 Last quarter
5 2 Current quarter
6 0 Current quarter
7 1 Last quarter
8 2 Current quarter
9 0 Current quarter
10 1 Last quarter
11 2 Current quarter
12 0 Current quarter

By your postings only first month of quarter to display last quarter.
Therefore ---
IIF((Format([YourDateField],"m") Mod 3) =1, Last_Quarter, Current_Quarter)

--
KARL DEWEY
Build a little - Test a little


Leviathan via AccessMonster.com said:
Karl,

Thanks for your help. One quick question, since 2 MOD 3 = 1; the IIF
statement would have to be IIf((Format([PAID_DATE],"m") Mod 3)=1 And Format(
[PAID_DATE],"m")<>"2","Last_Quarter","Current_Quarter"), right?

KARL said:
So for your IIF statement use this ---
IIF((Format([YourDateField],"m") Mod 3) =1, Last_Quarter, Current_Quarter)
If it was February... it'd restrict the quarter to 1/1/2007 - 2/1/2007.
Basically, the reports we run are always for the previous month... so we'd
[quoted text clipped - 12 lines]
=DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1) And <DateSerial(Year(Date()),Month(Date()),1)
 
L

Leviathan via AccessMonster.com

Oh, you're right - messed that one up.

Thanks.

KARL said:
No.
Month Mod 3 Display quarter
1 1 Last quarter
2 2 Current quarter
3 0 Current quarter
4 1 Last quarter
5 2 Current quarter
6 0 Current quarter
7 1 Last quarter
8 2 Current quarter
9 0 Current quarter
10 1 Last quarter
11 2 Current quarter
12 0 Current quarter

By your postings only first month of quarter to display last quarter.
Therefore ---
IIF((Format([YourDateField],"m") Mod 3) =1, Last_Quarter, Current_Quarter)
[quoted text clipped - 9 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top