calculating maturity dates..

[ai_enjoi via AccessMonster.com]

i have here for my database some investments...
and then, for every investment, there are different terms which the user will
choose from...
either days, months, or years.. and then i want the maturity date to be
calculated using what the user had inputted...
for example he indicates 3 months, the starting date is Feb 28, 2007, i
want the maturity date to be calculated as May 30, 2007... how is this
possible? and for years, if example Feb 28, 2007 to Feb 29, 2008?

Please...i really need a help on this...thank you verymuch....

 

[Rod Plastow]

The DateAdd function will be at the heart of your calculation so read up on
that.

You will need to branch within your code depending upon whether you are
dealing with days, weeks, months or years.

Your examples suggest that you want to add one day to the specified
interval. So having calculated the result use the function again to add one
further day.

Rod

 

[Dale Fye]

I would have a table that contains the "terms" of the investment and the
appropriate interval parameters from the DateAdd function (I'd actually
probably use this table as the datasource for the users selection of the
term). So tbl_InvestTerms would have contain two columns and have values
like "yyyy", "Years", "q", "Quarters", "m", "Months", "d", "Days". This
table would be the datasource for cbo_InvestTerm, where you would display
the second column but store (bind) the first column.

Then, in your query, you would create a MaturityDate column similar to:

MaturityDate: DateAdd([Term], [TimePeriods], [StartDate])

HTH
Dale

 

[Rod Plastow]

Good suggestion, Dale. In fact why bother with a table, simply code the
values directly into a list or combo box?

Rod

 

[Dale Fye]

Rod,

I hate to "simply code the values directly into a list" primarily because it
is so easy to forget what you've done and have to go looking.

Personally, I prefer to have tables behind all of my bound controls.
Especially if there is any chance the the values will be used in more than
one place. I cannot think of a single case in the last 5 years where I have
hard-coded values in a combo or list box.

Dale

 

[Rod Plastow]

Hi Dale,

I agree in principle but in this case the text strings - "yyyy", "mm", etc.
will not change. Once coded it is unlikely you need to revisit the control.

Rod

 

[Dale Fye]

Rod,

This is just a personal preference thing. I like to have "all" of my data
in tables.

Dale