Schedule Task and update next task time 1

[Bullsandbears123]

I have a table that I use to schdule tasks.
[Task] {date] [Howoften]

What I'm trying to do is change the [date] field to the next time the event will run.
Example:
[howoften] has a list : daily, m-f , weekly, monthly,yearly

When one is chosen I need a function to change [date] to the next time the task needs to run. I understand how to do it yearly(date+365), weekly(date+7), daily(date+1),etc., but my problem is running it monthly on the last day of the month. Because some months have 31 days 30 days 28 days it goofs me up. If I put (date+30) it might not be on the last day every month. Any and all ideas are welcome. THanks you.

YOUR HELP IS VERY MUCH APPRECIATED!!!

PS. I use a schduled task that runs daily that looks up a table and run any task for the day dependent on the [date] field. I then need to change the date field for the next time. I have a lot of events that change all the time, so using task scheduler on an individual basis would confuse the people using the GUI I designed.
THANKS AGAIN!

 

[seagoing]

You need to investigate the VBA Function DateAdd()

Prototype:
DateAdd(interval, number, date)

Where interval is
yyyy for whole year
q for quarter
m for month
y for day of year
d for day
w for weekday
ww for week
h for hour
n for minute
s for second

and number is the number of the above intervals to add
and date is the date you are adding to

for example to get the date 2 months from today:

dNewDate = DateAdd("m", 2, "01 July 2003&quot

Should work!!

 

[Bullsandbears123]

Thanks for the help, this is really Helpful!!!

 

[JDavey]

The way I handled this in a project was to use a function that adds one month to the date passed to it, then subtracts one day from the result. This gives me the last day of the month. Here is the VB function I used:

Public Function GetLastDayOfMonth(sDate As Date) As Date
Dim sMonth As String
Dim sYear As String
Dim dtTempDate As Date

'get first day of current month
dtTempDate = GetFirstDayOfMonth(sDate)

'add one month to that date
dtTempDate = DateAdd("m", 1, dtTempDate)

'subtract one day to get the last day of the month
dtTempDate = DateAdd("d", -1, dtTempDate)

GetLastDayOfMonth = dtTempDate

End Function

Public Function GetFirstDayOfMonth(sDate As Date) As Date
Dim sMonth As String
Dim sYear As String
sMonth = Month(sDate)
sYear = Year(sDate)

'set first day of month
GetFirstDayOfMonth = CDate(sMonth & "/1/" & sYear)
End Function

 

[Bullsandbears123]

Great Idea!
Thanks

 

[EnS]

Just an idea, but why not use the DateSerial function?

For example, if your process automatically runs on the last day of each month, then you may want to set your next run date to the last day of the next month automatically after your current process is finished running. Therefore, if your process runs on 06/30/2003, you'll want to set it to run again on 07/31/2003, right?

Assume that your process will run and finish on 06/30/2003. To automatically update your current run_date to the next date_run, use Date() + 1, which will return the first day of the next month (07/01/2003) since your current system date (hopefully) will be the last day of the current month (06/30/2003), within the DateSerial function to get the next months last date.

With this in mind, the DateSerial for the last day of the next month would be:

DateSerial(DatePart("yyyy",Date()+1),DatePart("m",Date()+1),DatePart("d",(DateSerial(DatePart("yyyy",Date()+1),DatePart("m",Date()+1)+1,1)-1)))

Also, you may use this function in either a simple update query or in code.

Hope this helps.


ERM