Autofill date field in a form and add a period of time to the date

[Jungle153]

Good morning all,

I am designing a database to administer courses. When setting up a course a renewal date needs to be set, this is based on the end date of the course and how long the certificate is valid for.

The period of validity varies from course to course and this information is stored in the tblCourseInformation. This is stored as an integer.

The course end date and renewal are stored in the tblCourseAdmin.

The for I have designed incorporates fields from 3 tables and the data populates automatically when a course is selected from a combobox.

I've tried using the DateAdd funtion in the after_update section in properties (and in the defult value). Neither of these have worked for me {Example: =DateAdd([yyyy],[Renewal Period (Years)],[End Date])}.

I've also tried manipulating an excel formula in the code builder, whith no joy (probably due to a complete lack of coding skills).

Any help will be gratefully recieved.

Cheers
Gareth

[twinnyfo]

Gareth,

Welcome to Bytes!

The Format for your DateAdd() Function should be exactly as follows:

=DateAdd("yyyy", [Renewal Period (Years)], [End Date])

I am also assuming that the items in square brackets are Field Names? As long as Renewal Period is an integer and End Date is a valid date, this formula should work.

Please let us know how this resolves your issue.

Hope this hepps!

[NeoPa]

Hi Gareth.

If you look up in Help for the DateAdd() function you'll see that the first parameter (Interval) is a string value. A string value is delimited by double-quotes (") in VBA and single-quotes (') in SQL and The Expression Service.

When a reference is made to text within square brackets as you have it then Access (whichever part you're using) will try to reference a Field, then a Control. If it doesn't find a valid match for either it will use the text between the brackets to prompt the user on the screen for a value. While this may be the desired approach for [End Date] it's unlikely to be so for [Renewal Period (Years)] and is almost certainly wrong for [yyyy].

I hope that makes the problem clear.