By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,273 Members | 1,892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,273 IT Pros & Developers. It's quick & easy.

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

P: 1
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.

2 Weeks Ago #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 3,364

Welcome to Bytes!

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

Expand|Select|Wrap|Line Numbers
  1. =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!
1 Week Ago #2

Expert Mod 15k+
P: 31,660
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.
1 Week Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.