Access table function: simply adding months to a date

Question

Having trouble getting a simple task to work. I have a field that is populated with number of months.....could be 24, 36.....whatever. I have another field defined as

date/time with short dates in it. I need to add the number of months to this field to populate a new field in the date/time short date format. Nothing seems to work and I've done this in Access 2003. Haven't used Access in a very long time and now I'm using 2016. Should be an easy solution but keep getting errors. Even tried in a query format and no success.

Answer 1

Unfortunately you don't tell us what you have tried and what errors you got.

The most obvious solution is to use the dateadd function.

Something like:

DateAdd("m",NumberOfMonths,YourDate)

Answer 2

You stated "I used the "DateAdd" function in the criteria line of [future date]". The criteria line is to create a filter of your records. If you want to update a field, you need to switch you query to an Update Query. However, I agree with all others that you shouldn't store this value.

Answer 3

Yes, you are all right. Let's start with the query....

I have a master table, only 3 fields of concern...past date, future date, number of months.

past date and future date are date/time data types.

number of months is a number type. Integer. This field needs 2 digits for number of months and will always be number of months.

The query I set up: Has all three fields (and some other fields of no concern), I used the "DateAdd" function in the criteria line of [future date].

Looks like: DateAdd("m",[number of months],[past date]), this would then change or fill [future date]. I've also tried DateAdd("mm",[number of months],[past date]).

The error is "Data type mismatch in criteria expression".

I'm assuming this is because the [number of months] is an integer and not date/time type data format? I know I'm missing something simple here but can't seem to put my finger on it.....

Answer 4

Scott's right. Storing calculated values is seldom needed, and usually undesirable. DISPLAY the calculated date where needed in a control on a form or report.

That said, sometimes we have a hard time seeing what a poster sees because we don't have direct access to their computer. When that happens, the best thing you, the poster, can do is provide enough details about the specifics what you have tried, what the results were, and what the results expected or required should have been. Precisely.

Thanks.

Answer 5

As a General rule we do NOT store calculated Values. If you need to calculate a future date based on a field with a number of moths stored you would use the expression:

=DateAdd("m",NumMonths,datefield)

This expression can be used as the Controlsource of a control on a form or report or in a query.