Quarterly Restriction

[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?

 

[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")

 

[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.

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?

 

[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

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.

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)

 

[Leviathan via AccessMonster.com]

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 --
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]

 

[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]

 

[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

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)

 

[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.

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]

 

[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

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.

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)

 

[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?

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]

 

[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

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?

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)

 

[Leviathan via AccessMonster.com]

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

Thanks.

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,

[quoted text clipped - 9 lines]