set up values in table based on start and lenght of period

[mato61]

Hello guys,

maybe simple question but i cant go over it for a while.
I have a table period with three columns
ID, Period_Start_Year, Period_End_Year

I could have many periods in a future. What i want is to allow users to insert just first start year and lenght of period and table will be automatically updated based on these two values.

So user will insert Period_Start_Year and Lenght of Period, for example 2010 and 2 and table should be>
ID Period_Start_Year Period_End_Year
1 2010 2011
2 2012 2013
3 2014 2015
while i am wroting this i realised that user have to specify also ending year, i do not want to have infinity..
i am thinking about form and update query but cant get it...

 

[Old Man Devin]

Here's my suggestion:

Have a form with a period start, period length, and period end box, with the start and end boxes bound the start and end fields in your table respectively.

Then in the After_Update event of the period length box, have some VBA code that sets the period end.

Me!Period_End_Year = Me!Period_Start_Year + Me!Period_Length

(assuming you call the box for period length Period_Length, and that all three boxes are just a year value i.e. a number)

If the data type of the field is an actual date rather than a number, when you'll need to use the DateAdd function to calculation your period end, which you can easily find via Google.

You'll probably want to play with Nz functions to make sure it copes with the boxes being empty.

Hope this helps!