Sara has attended:
Access Introduction course
Creating a field to automatically insert dates from a formula
For example, in our personnel database I am amending. I would like to enter the employee start date and then have the probationary date automatically complete for 3 months from that date. Could you explain how I would do this?
Many thanks.
RE: Creating a field to automatically insert dates from a formul
Hi Sara
Thanks for your question.
To automatically add 3 months to an employee's start date:
1. Create a new query in Design view.
2. From the Tables list, select the table/s that contain the information you want displayed in the query (presumably the table/s that contain employee name and/or ID and start date), then click the Close button.
3. Double-click on the fields you wish to add to the query.
4. In the next available column in the query design area, enter the name you want the field to have (let's call it Probation end date) followed by an expression that will add 3 months to the date in the start date field as follows:
Probation end date:DateAdd("m",+3,[Start date])
To explain:
DateAdd is used to add or subtract a specified amount of time from a field of Date/Time type. "m" indicates that it is months that we are dealing with; +3 indicates that we want to add 3 units of the time period we are dealing with (you would enter a minus symbol to 'subtract' time) and [Start date] (or whatever this field is called in your database) is the field you are adding time to.
5. Make sure there is a tick in the box in the Show: row underneath the Probation end date field.
6. Change to Datasheet view to check the query is working, then save it.
I hope this helps.
Amanda
