# Thread: Figuring out the next Quarter Date

1. Registered User
Join Date
Feb 2002
Posts
55

## Unanswered: Figuring out the next Quarter Date

I currently have two fields: ANS_DT and RVW_DT. The first date is the date a question is entered. The second date should be the next work quarter for the question to be reviewed.

For example:

If the ANS_DT is a value of JAN 1 To MARCH 31, 2002 then RVW_DT should appear as APRIL 1, 2002.

Any help would be appreciated!

TIA,

HIRK

2. Registered User
Join Date
Apr 2002
Location
Portugal
Posts
146
Hi!

use the dateadd function like this:

In the event procedure AfterUpdate of the question date field,
type in code this:

[field name question to be reviewd]=dateadd("q",1,me![field name question date]

the q indicates that is quarter, and 1, just tells the function to add 1 quarter to the question date.

Note: your filelds type data in the table must be set to date/time

3. Registered User
Join Date
Feb 2002
Posts
55
Thanks Miguel! That makes it 99% what I am looking to do. The problem is that it is adding 1/4 date (e.g., if you put in 1/4/2002 you get 4/4/2002).

What I need is for it to go to the 1st day of the first quarter (e.g., 1/4/2002 would be 4/1/2002).

Thanks...

4. Registered User
Join Date
Apr 2002
Location
Portugal
Posts
146
Hi again!

You can do it, manipulating the elapsed time in days. For example:

[day of question]=15/01/2002 Note my date format is: Day/Month/Year

AfterUpdate Event of [day of question]:
Dim Elapsed as Integer
Dim FirstDay as Date

Elapsed=DatePart("d",Me![day of question])-1 ' This is the number of days elapsed until the date of question

FirstDay=DateAdd("d",-Elapsed,Me![Day of Question]) ' Subtracting the elapsed days you return to the first day of the month

Praticaly we have: Elapsed=14
FirstDay=15-14/01/2002 => 01/01/2002
Now adding a quarter we have: 01/04/2002

Hope this help!!!

Ciao!

5. Registered User
Join Date
Feb 2002
Posts
55
Miguel,

Is it possible to set it so it goes to the quarter to which the date belongs rather than just adding a quarter?

If I put in 5/28/2002 I get 8/1/2002 when I am looking to put it in the 2nd quarter it belongs to which would be 7/1/2002.

Thanks again and thanks for all of your great input so far!

Hirk

6. Registered User
Join Date
Apr 2002
Location
Portugal
Posts
146
Hirkos,

If I understand, you want the first month of the quarter, in your example:

05/28/2002 => 08/01/2002 this is the next quarter relative to the date
but August is the 2.º month of the quarter

Do you want the first month of the quarter?

if so then you have to manipulate dates and use Ifs statments, indicating to what quarter belongs the month.

Take a look at this functions:
DateDiff("d",previous date,next date)
DatePart("d",date you want)

d - is string for days
ww - is string for weekends
w - is string for week
m - is string for month
y - is string for year
h - is string for hour
n - is string for minutes

for example:

AfterUpdate Event of [day of question]:
Dim Elapsed as Integer
Dim FirstDay as Date

Elapsed=DatePart("d",Me![day of question])-1 ' This is the number of days elapsed until the date of question

FirstDay=DateAdd("d",-Elapsed,Me![Day of Question]) ' Subtracting the elapsed days you return to the first day of the month

If DatePart("m",[Day quest review])=2 or DatePart("m",[Day quest review])=3 then
[day quest review]=DatePart("d",[Day quest rev]) & "/" & 1 & "/" & DatePart("y",[Day quest rev])

ElseIf DatePart("m",[Day quest review])=5 or DatePart("m",[Day quest review])=6 then
[day quest review]=DatePart("d",[Day quest rev]) & "/" & 4 & "/" & DatePart("y",[Day quest rev])
.
.
.
and so on.

you set the interval not just adding 1 quarter to the date, but comparing the new date with the months of the quarters.
I don't know how you are defining the begining quarter, i consider january for first month of first quarter.

7. Registered User
Join Date
Feb 2002
Posts
55
That added knowledge you gave me did the trick... I set up an If...Then and based on the results output the start date of the next quarter.