Expert: Scottgem - 7/13/2009

Question
QUESTION: Hi I am trying to set up a training database, some of the courses dont expire but some do.  i have entered an expiry field on the course form, which i am going to either put years or months. So if a course expired after 3 years in this field i will either put 3 or 36 depending on which ever is easiest.

my question is though how can i get ms access to record the expiration date using the StartDate field and the Expiry date field, on the attendees subform??

ANSWER: As a general rule we do not store calculated values. So there is no need to store this information. But you do need to display it. So what you would do is set the ControlSource of the Expiry control on the subform like this:

=DateAdd("m",Forms!mainformname!Expiry,[StartDate])

I would use months for the expiration period so you don't need to try and determine whether the period is months or years.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: I have one last question. I have got the forms working perfectly but now i need a report that takes the expiry date from the attendees subform and put this on a report.  Also I need the report to have a parameter, so if I put the year 2010 in it will show all the attendees who's courses expire that year.

ANSWER: You do this in a query. In your query add a column like so:

Expiration: DateAdd("m",[Expiry],[StartDate])

I would add a second column:

ExpYr: Year([Expiration])

I would set the criteria for that column to:

[Enter Year]

I would then create a report based on the query.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi Scott,

is it possible to use the =dateadd to a table, ive tried =DateAdd("m",Tables![Events]![Expiry],[StartDate])

but i just get #name?

Answer
As a general rule we do not store calculated values. There is no reason to store this and every reason not to. Just use the expression where you want to display the value.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA