Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Date Manipulation (2002)

    Hello again.. I am having some difficulty with dates. I have a field that tracks Cycles. The cycles are A, B, C, D & E. Each cycle corresponds to a year. For example plans on Cycle A have to be redone in 2007, B in 2008 C in 2009, D in 2010 & E in 2011. But once we get to 2012 the cycle starts over, and A now becomes 2012 etc.. How can I make the database know what cycle each plan is on by the year that it is and then reset when we have completed one full rotation of A,B,C,D, & E... I HOPE THIS MAKES SENSE!!!

    (It may be a mute point because in 2012 they probably wont be using this database... WHO KNOWS!) But your help is always greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Manipulation (2002)

    You can calculate the cycle in a query.

    If you have a field YearField containing the year, the following expression will calculate the corresponding cycle:

    Cycle: Chr(65+([YearField]-2) Mod 5)

    If you have a date/time field DateField that should be used for the computation:

    Cycle: Chr(65+(Year([DateField])-2) Mod 5)

    First, 2 is subtracted from the year, so 2005 becomes 2003 etc.
    The Mod operator calculates the remainder after division by 5. For 2003 this is 3.
    We add 65 (the ASCII code for "A") to this, in our example we get 68.
    The Chr function returns the corresponding character, "D" in our example.

  3. #3
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date Manipulation (2002)

    I actually have it the opposite way. I have a field called RAPCycle which has the letters to choose from (A,B,C,D or E) this will never change for the plan once they have been assigned a letter they will always keep that letter. It is the year that changes. Plans with A cycle have to be restated in 2007, 2012, 2017, 2020 etc. plans in B cycle restate in 2008, 2013, 2018, 2021 etc. What we want is to have a field on our form that says this plan's cycle ends on January 31, [Then calculate the year basied on the Letter]. I had it hard wired where A was equal to 2007, and B was 2007 +1 etc which works, but once the year passes, it won't recalculate unless someone changes it manually once a year. Which really won't work.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Manipulation (2002)

    OK, try this:

    =Year(Date())+(Asc([RAPCycle])+2002-Year(Date())) Mod 5

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Date Manipulation (2002)

    Here's an alternative, if i'm correctly understanding what you are looking for. I'm not sure when the first year Plan A was available, but let's say it was 2002. The Cycle for any plan could be determined by:

    Cycle = Choose( (((year(plandate)-2002) mod 5) + 1, "A", "B", "C", "D", "E")

    You never have to reset anything.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date Manipulation (2002)

    Thank you once again with 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
  •