AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
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:
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
