Results 1 to 7 of 7
  1. #1
    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. #2
    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. #3
    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. #4
    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

    [Day question review]=DateAdd("q",1,FirstDay)

    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. #5
    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. #6
    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)
    DateAdd("d",number as double,date you want)
    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

    [Day question review]=DateAdd("q",1,FirstDay)

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

    Thanks for all your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •