Adding one month to a date to get the whole year

[DBAchick]

I searched the forum, but could not find this issue addressed. Access 2000.

I have a query in which one field is a date. I have parsed out MyMonth: MyMonth = Month([MyDate]) and now want to perform a function on MyMonth to get the month 6 months from now, My6Month.

Example: If MyDate is 09/30/2000, MyMonth is 09. If I just add 6 (for 6 months) I get 15 and there is no 15th month (at least on my calendar!)

So, what can I do to my result of '09' to get '03'? and still have a result of '04' give me '10'?

Any help is appreciated.

 

[braindead2]

look at the dateserial function under access help

?dateserial(2002,month(date)+7,13)
1/13/2003

or the dateadd() function

 

[scking]

So perform your date addition BEFORE you get the month or use MOD arithmetic if you insist on using the month only. As a challege here is the MOD arithmetic

Add the current month number to the number of months you
want to add. So, since today is June you would add 6 to the number of months you want to add which is six. This process is needed to normalize for mod 12 arithemetic.

?(8+6) mod 12
2

Dim intRem As Integer
Dim CurMonth As Integer
Dim NumMonths As Integer
Const AddMonths As Integer = 6

CurMonth = Month(Date())
NumMonths = CurMonth + AddMonths
intRem = NumMonths Mod 12
If intRem = 0 Then
intRem = 12
End If

Then intRem would contain the month number for whatever year after adding the AddMonth.

Alternative to get the month:
Month(DateAdd("m", 6, Now))

Recommended alternative:
DateAdd("m", 6, Now)
----------------------
Steve King
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------