Change a month of a date on a recordset

I need to create a running 60 month forecast end date, so if the current month changes I need for the forecast end date to reflect the month change.  However, if the current forecast end date has a hard coded date that equals the current year I do not want to change the date.

I am looking for function that will accomplish this task>

Karen

The code below was my first attempt, however, it returned the wrong dates.  Then I got to thinking there may be a simpler approach.

 ie. current ForecastEnddate = 1/1/2016 and the Current month is February the new ForecastEndDate = 2/1/2016, unless
 ForecastEnddate's year = current year then nothing happens.

Public Function UpdateForecastMth()
' Procedure: UpdateForecastEndDate
' DateTime: 2/10/2011 2:21:53 PM
' Author: Karen F. Schaefer
' Description: Verifies the Maximum ForecastEndDate and adds 1 month if current Forecast date <= current month
'  ie. current ForecastEnddate = 1/1/2016 and the Current month is February the new ForecastEndDate = 2/1/2016, unless
'      ForecastEnddate's year = current year then nothing happens.
'--
Dim pMth As Date
Dim pYear As Date
Dim strSql As String
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb()

strSql = "Select ForecastEndDate from tblEmp_ForecastStatusing"
Set rs = db.OpenRecordset(strSql)
    Do Until rs.EOF
        pMth = Month(rs.Fields("ForecastEndDate").value)
        pYear = Year(rs.Fields("ForecastEndDate").value)
        
        If pMth < Month(Date) And pYear > Year(Date) Then
            rs.Edit
            pMth = pMth + 1
            rs.Fields("ForecastEndDate") = Format(pMth & "/1/" & pYear, "mm/dd/yyyy")
            rs.Update
        End If
        rs.MoveNext
    Loop
End Function

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
wobblynutCommented:
Try replacing lines 19-27 with  

If rs.Fields("ForecastEndDate").value>date+366 then
            rs.Edit
            rs.Fields("ForecastEndDate") =  dateserial(year(date)+5,month(date), 1)
            rs.Update
        End If
       
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks that did the trick partly, I still need to check if the date in Forecast end date contains the current year - meaning the date was manually entered than I do not want to change that date.  Got any suggestions on how to look for current year in this field and if so skip it.

K
0
 
wobblynutCommented:
What that above does is to look for anything within the next year rather than the current calendar year.  Else when you get to December, anything that you've given a January date will be overwritten.

If you definitely want t only leave the current calandar year then replace first line of previous solution with ....

If year(rs.Fields("ForecastEndDate").value)>year(date) then

hope that helps.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Gustav BrockCIOCommented:
You could and should operate with dates only:
Dim datForecast As Date
' ...
    Do Until rs.EOF
        datForecast = rs.Fields("ForecastEndDate").Value
        pYear = Year(datForecast)
        If pYear > Year(Date) Then
          rs.Edit
            rs.Fields("ForecastEndDate") = DateAdd("m", 1, datForecast)
          rs.Update
        End If
        rs.MoveNext
    Loop

Open in new window


/gustav
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Gustav,

This doesn't give me a running date = Current date (month and Year)  plus 60 months.  the date in question will always be the first of the month.

K
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
after retesting the suggestions these 2 did the trick.

Thanks for the input.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.