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>
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
pMth = pMth + 1
rs.Fields("ForecastEndDate") = Format(pMth & "/1/" & pYear, "mm/dd/yyyy")