New Record, Different Date

[Zippyfrog]

Currently I am working on a program to track athletic contests at the high school I work at. (Current program does everything, but way too costly each year). One feature we need is a "rollover" feature, where a button can be added to copy a record but change the date by one year. Not literally 365 days, but corresponding dates.

Example: An event occurs on Friday, July 20, 2007, a button on the form would allow a single click to copy all the information into another record with the exception of the date being Friday, July 18, 2008.

How would I go about coding that into a button?

 

[Bilbo_Baggins_Esq]

Is all of the information contained in one table?

 

[Bilbo_Baggins_Esq]

well, regardless of where all the fields are located, you could write a query that pulls all of the data related to the current instance.
Then write an append query that will append everything but the date from that first query. In the date field append the value of a public function, and have the public function open a input box where you input the new date value.

If everything comes from the same table, great, if not, then gather it all up from the different tables in your first query.

 

[Zippyfrog]

Yes, all the information is in a single table. The setup is the primary key is an auto number, and every other piece of data in the record needs to be copied identically into a new record, except for that it is one year later on a corresponding day.

I can figure that part out, but the part I can't figure out is how to keep corresponding dates, because there are leap years thrown in there. Would it just involve adding 364 to the total number of days? To me, that would be logical at first glance, but isn't there a point at which you would need to "push" the schedule back to the correct corresponding week?

 

[Bilbo_Baggins_Esq]

I thought I surmized from your first post that it may be "about" one year later, or it may be exactly one year later.

When I looked at your example, the date was different by two days, so I assumed the new date might be close, but slightly different.

If you use the described method above, you can manually enter the new date (except omit the AutoNumber field as this will auto populate).

For the same date, only one year later, simply add 365 to the original date except for leap years.

Are you needing help ascertaining if a leap year day is involved?

 

[Bilbo_Baggins_Esq]

For leap year calculations in your case, here is how I would do it (it would not surprise me at all to see somebody post a more direct route)

To handle all the different scenarios, it makes a complicated looking series of nested IIF statements.

The basis I use for this determination is the premise that the year number for all leap years is evenly divisible by 4.
All other years are not evenly divisible by 4.
Example:
2007/4= 501.75
2008/4= 502 (LEAP YEAR)
2009/4=502.25
2010/3=502.5
2011/4=502.75
2012/4=503 (LEAP YEAR)
Etc etc

The method I use to ascertain this uses the Int function which strips a result down to just the integer portion of a result.

OK, first determine if you're currently in a leap year as follows
IIF ( ( year ( origin_date ) ) / 4 = int ( ( year ( origin_date ) ) / 4 ), TRUE (LEAP YEAR), FALSE( NOT LEAP YEAR))

Now we’ll deal with what to do if the current year is a leap year.
You’ll need to ascertain if the origin_date is effected by the extra leap day.
Essentially, any date that is 2/29 or earlier is effected by it and any date after 2/29 is not affected by it.
So, in the “TRUE (LEAP YEAR)” section of the of the original IIF above
IIF ( month ( origin_date ) = 1, origin_date + 366, IIF (month ( origin_date ) = 2, TRUE (FEBRUARY), origin_date + 365 ) )

You’ll also need to decide what to do if the origin date is in fact the leap date. Essentially, you’ll need to either make the next year 2/28 or 3/1.
For purposes of this exercise, we’ll say you want to treat any leap date event as if it were really 2/28
So, in the “TRUE (FEBRUARY)” section of the second IIF above, you need to first test if the month of the origin_date is either January or February and then if it is the leap day.
IIF ( day ( origin_date ) = 29, origin_date + 364, origin_date + 366 )

This finishes the scenarios where the year of origin_date is a leap year as follows:
366 is added to any origin_date EARLIER than 2/29
364 is added to origin_date if origin date is 2/29
365 is added to all other origin_date values


Next, if the year of the origin_date is not a leap year you’ll need to ascertain if the year AFTER origin_date is a leap year.
So, in the section of the original IIF at the top where it says “FALSE( NOT LEAP YEAR)” you’ll need to test with another IIF similar to the first.
IIF ( ( year ( origin_date ) +1 ) / 4 = int ( ( year ( origin_date ) + 1 ) / 4 ), TRUE (NEXT LEAP YEAR), origin_date + 365 )

Now in section of that says “FALSE( NOT LEAP YEAR)” you need to ascertain if the if the origin_date + 1 year is effected by the extra leap year day. Essentially, any date for the origin_date which is LATER than 2/28 is effected, and any other date (2/28 or earlier) is not effected.
This is similar to the method for if the year of origin_date IS a leap year, only the exact opposite (except that you don’t need to check if origin_date is the leap day).
IIF ( month ( origin_date ) <=2 , origin_date + 365, origin_date + 366 ) )

This finishes the scenarios where the year following origin_date is a leap year as follows:
366 is added to any origin_date later than February
365 is added to all other origin_date values.

So the whole nested mess would look like this:
IIF(( year(origin_date))/4=int((year(origin_date))/4), IIF(month(origin_date)=1, origin_date + 366, IIF(month(origin_date )=2, IIF(day(origin_date)=29, origin_date+364,origin_date+366), origin_date+365)), IIF((year(origin_date)+1)/4=int((year(origin_date)+1)/4), IIF(month(origin_date) <=2, origin_date+365, origin_date + 366)), origin_date+365))

I could have just written it all out, but I think you should know that all the scenarios were taken into account and hopefully help you understand the method I use to account for different scenarios.

Once again, it would not surprise me for somebody else to provide a more direct method, but this is what I’ve used successfully in several apps.

I hope this helps!

 

[Bilbo_Baggins_Esq]

That's why it's good to have numerous people look at a problem in forums.

Sometimes it's hard to see the forest because of all the trees in the way.

That CLEARLY is what he's looking for (the day of the week), and it's a good thing that answer wasn't a poisonous snake or I'd of been dead a long time ago.

I even remembered the Dateadd function, but for some reason stayed on that more complicated path.

DOH!

 

[missinglinq]

Addendum: After posting my answer I realized that while my answer was correct , that his answer was correct also! See below:

I love trying to figure date stuff out, but this one had me going for a bit. Then the answer dawned on me! If I understand your post correctly, you need a date approximately one year later but on the same day of the week; is this correct? If the date concerned is a Friday, say, you need the date for a Friday approximately one year later. If this is the case, you were right to begin with, and we were all trying to over engineer the problem!

364 days = 52 weeks, and adding 52 weeks to a date will give you a date one "year" later on the same day of the week! So your OriginalDate + 364 days will work! Leap year won't affect this! The only thing a leap year will do is determine whether the difference in the day/date (the 21 of July 21, 2007) is one or two days different from the original date.

Good Luck!

Linq