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?
current month, to restrict to the current quarter, any ideas?
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?
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)
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.
[quoted text clipped - 9 lines]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
Sorry for the confusion, thanks.
[quoted text clipped - 9 lines]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 --
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.
[quoted text clipped - 9 lines]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 --=DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1) And <DateSerial(Year(Date()),Month(Date()),1)
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.[quoted text clipped - 16 lines]One other issue...
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.[quoted text clipped - 16 lines]One other issue...=DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1) And <DateSerial(Year(Date()),Month(Date()),1)
KARL said:So for your IIF statement use this ---
IIF((Format([YourDateField],"m") Mod 3) =1, Last_Quarter, Current_Quarter)[quoted text clipped - 12 lines]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
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)[quoted text clipped - 12 lines]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=DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1) And <DateSerial(Year(Date()),Month(Date()),1)
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]Karl,
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.