Adding months to one date field to populate another..

**JTKrol** · 06-10-2015, 01:53 PM

Hello,

I am new here and somewhat new to Access...

I am creating a db for work that has a a field called "Employee_start_date" that date I would enter... I have another field called "401K_Eligible_Date", I would like this field to populate automatically using the "Employee_start_date" field but add 6 months.

So if I enter in the Employee_start_date of 01/01/2015 The system would return 06/01/2015 in the 401K_Eligible_Date field.

How would I go about doing that. They are both in the same table. I am using Access 2013 on a windows 7 Laptop.

Thank you
Joe

**June7** · 06-10-2015, 01:58 PM

Saving this calculated value is not necessary, just calculate it when needed. Saving to table would require code behind a form.

**JTKrol** · 06-10-2015, 02:03 PM

Every time I put an employee in I need the information in the table. I will be querying it reporting on it etc... Even still how would I calculate it when needed? I don't know how to perform the calculation.

**ItsMe** · 06-10-2015, 02:11 PM

You could use the DateAdd() function. Halfway down is an example of adding one month to a date.
https://msdn.microsoft.com/en-us/lib.../gg251759.aspx

**JTKrol** · 06-10-2015, 02:27 PM

Thank you for your response.

I did see that, and tried to apply it but could not figure out how to call the date from the field I have already entered and not as an input. The example has you imputing a date. Is there a syntax that I need to use to call a field value from the table as the date to be calculated against?

**June7** · 06-10-2015, 04:44 PM

Just reference the field as one of the function argument parameters.

DateAdd("m", 6, [fieldname])

In a query:

SELECT *, DateAdd("m", 6, [fieldname]) AS EligibleDate FROM tablename;

**ItsMe** · 06-10-2015, 05:45 PM

You want your code to look like what June posted there. Even if it does not look like that, post the code that you have tried. Tell us why you tried it and why you thought it might work. This way, we will know how to advise you. Otherwise, we will only be able to guess where you are struggling.

**JTKrol** · 06-10-2015, 06:55 PM

Thank you I will try it!!!

**JTKrol** · 06-10-2015, 07:25 PM

OK,

I came up with an error.

I had to create a new field because it would not allow me to change the existing field to a calculated field. So I created a new field called 401K_Eligibility_Date made it a calculate field and pasted the DateAdd("m",6,[Dateofhire]) in the calculation window.
I then saved the table and came up with an error message... I can't copy and paste it so here are the major parts of the error.

"Errors were encountered in the Save Operation. Fields were not added. Properties were not updated." When I expanded the window I get... "This error can be caused by one of the following: The maximum number of columns in a table or the maximum number of locks for a single file has been exceeded. The index property of a field is changed from Yes (Duplicates OK) to Yes (No Duplicates) when duplicate data exists in the table. An expression is not specified in the Expression property of a calculated field. It then goes on about how to change the registry etc... I have attached a screen shot in the hopes that will help...

Click image for larger version.

Name: Access dateadd problem.jpg
Views: 14
Size: 253.6 KB
ID: 20975

**June7** · 06-10-2015, 07:51 PM

Not all functions are available to calculated field in table. DateAdd is not available.

**ItsMe** · 06-11-2015, 03:27 AM

Here is a sample DB with some examples. There is a query with some Alias' that I created. One of the alias' uses June's example. The other alias' extract the month or the day from a date. The alias' that extract a month or a day are just to demonstrate there are different date functions out there. You do not want to use these to add months or days to an existing date.

There is a form in the DB. If you open the form, you will see the different fields from the query. There is also a button that use VBA code to add TWO months to the EligibleDate alias. Maybe this will give some ideas how to accomplish your task at hand.