Thread: Date Manipulation (2002)

1. 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. 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. 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. Re: Date Manipulation (2002)

OK, try this:

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

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

6. 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
•