Adding months to date to get future date

[hafiff]

I have a date field and customer will give a number of months when they can pay. I need a formula in Access for a calculated field for [Date] plus months = future date. Do I need to have the calculated field on the Form or Query?

I would appreciate if anyone can help writing a simple formula for the above.

Thanks,
HA

 

[Micron]

Whenever you need a function/expression, go here https://www.techonthenet.com/access/functions/
For your post, look at DateAdd. Your field should not be named Date if it is "[Date]"
To answer your other question, either will do. Having it in the query means it will be usable other ways, such as a report. Maybe you already know, but I'll mention as well that you should not store calculations, except for rare circumstances.

 

[UncleCatwoman]

Also take a look at the DateSerial function.

 

[Micron]

Also take a look at the DateSerial function.

Could you please provide an example of how one can add a date interval such as days, months or years, to a date by using that function? I'm always looking to expand my repertoire of function applications and thought that one was only for converting something that looks like an improperly formatted date to a date that can be recognized by the system. Thanks in advance, and welcome to the forum.

 

[UncleCatwoman]

DateSerial basically takes numbers and makes them into a date. The syntax is DateSerial(Year, Month, Day).

So, if you're looking at a mmddyy text string you'd use:
DateSerial(CInt("20" & Mid(mmddyy,5,2)), CInt(Mid(mmddyy,1,2)), CInt(Mid(mmddyy,3,2)))

But if you want to add a month to an existing date you can do:
DateSerial(Year(ExistingDate), (Month(ExistingDate)+1), Day(ExistingDate))

I have two functions I use for first day of the month (FOM) and last day of the month (LOM) for a date:
FOM = DateSerial(Year(ExistingDate), Month(ExistingDate), 1)
LOM = (DateSerial(Year(ExistingDate), (Month(ExistingDate)+1), 1) - 1)

Hope this helps!

UC

 

[Micron]

Well, I guess I'd do all that if the date didn't resemble a date as in your example.
Otherwise, I can't imagine why you'd do all that if you could just DateAdd("m",3,Forms!frmMyForm.[myDate]) or even just DateAdd("m",#3,06/15/2016#).
I think the OP does have a valid date since they said they have a date field they want to add to. BTW, I think your expression would work as
DateSerial("20" & Mid(mmddyy,5,2), Mid(mmddyy,1,2), Mid(mmddyy,3,2))
For getting the FOM or LOM, you're definitely challenged without a valid date to begin with. Otherwise, it could be simpler, as
DatePart("d",DateSerial(Year(#11/03/2016#), Month(#11/03/2016#), 1))
Thanks for sharing your info.

 

[xenou]

I typically use dateserial the same way UC describes, for what it's worth. I believe it requires integer parameters, so not not good so much for working with dates stored as text so much as just for creating dates out of integer values for year, month and day. Very useful for getting last day of month, first day of month so that's probably how I developed the habit of utilizing this construction -- often in conjunction with Year(), month() and day() functions.

 

[Micron]

I believe it requires integer parameters

Sometimes Access takes the position that if it looks like a number it is. This would be one of those times.
I tested DateSerial("20" & Mid(110516,5,2), Mid(110516,1,2), Mid(110516,3,2)) and it returned 11/5/2016. Most likely, if my system short date format was dd/mm/yyyy (which it is not) it would have returned a date in May instead of November. I didn't fail to notice that the year value returned was 4 digits even though the year portion supplied was not.

 

[xenou]

Yes, that works.
Dateserial("2016","12","31")