Need some help on recurring schedule dates

[Shinsuke]

Hi there. I'm currently on a project which takes care of preventive maintenance scheduling.

The job operations are such that they can occur once a year, once every 6 months, once every 3 months, once a month, once a week and one-off jobs.

The requirement is that I'm supposed to make a recurring scheduled start date for every jobs. That calculated start date will then be stored in the database and will be automatically generated on the scheduled start date field.

I'm just wondering whether this particular method is possible, or if it isn't, what's the next best solution to my problem? Just for you info, I'm a novice to Microsoft Access and only have basic knowledge of it.Thanks in advance.

 

[John Big Booty]

Have a look at the DateAdd(<interval>,<number>,<date>) function.

 

[Shinsuke]

Thanks for the link, but I don't think that's what I'm looking for.

Let me rephrase my question. What I'm looking for is to automate scheduled start date according to the schedule. For example, a halfyearly job which is done on 1st Jan 09, will automate the date 1st June 09 on the Scheduled Start Date field. This will loop on and on. Is that possible?

 

[John Big Booty]

Yes, what you would need to do is to test your schedule date against the current date if they are equal, you can then run an append query that uses the DateAdd function to add your next schedule date.

 

[John Big Booty]

Is this the sort of thing you are looking for?

 

[Shinsuke]

Is this the sort of thing you are looking for?

That's it! This sample database will definitely help out alot for my project. Thanks alot John!

 

[John Big Booty]

That's it! This sample database will definitely help out alot for my project. Thanks alot John!

Great but make sure you pull it apart and figure out how it works.

 

[Shinsuke]

Hi again. The database sample is really useful, but is it possible to automate the date as long as the current system date reaches the next date? It's not really a life and death situation, but its something that can be improved.

 

[John Big Booty]

It is certainly possible to automate the date. I only put the manually updated date so that you could quickly change dates and see what effect it had on the DB

You could put some code in the main forms On Load event to update the date to the current date, it would look something like;

Me.FieldName = Date()

This of course relies on the form being loaded at least once each day.

 

[Shinsuke]

Thanks for the help again John. I've a couple of problems with my database which I hope you'd able to help.

Firstly, when I incorporate the codings into my database application, there's some calculation problems. For example, the start date is 25th May 09, and I set the interval to 1 day. So, by right, it should display 26th May 09, right? But in my case, it displayed 26th August 09. What could be the cause?

My second problem is that my database is based on the job operations, followed by the start date. Is it possible to have different next dates for the different job operations? Cause in my application, it only display the next date for the 1st job on the table, and not for the rest. Every time I scroll to the next job to check the next date, it immediately went back to the 1st job. I hope my explanation make sense.

 

[Shinsuke]

Hi again. I managed to solve the miscalculation part a few days ago. I simply changed the DayMax to Me.ScheduledStartDate and it finally works.

I still have the other problem I'm still not able to solve. In one of the tables, I have a list of jobs that have different time schedule and duration. For example, Job A can be a monthly event, whereas Job B can be a yearly event.

I've managed to append the data to the table, there's some problem. I would like to change the interval rates and period type according to the jobs, but when I set to, let's say, Monthly as the period type and Interval to 1, all the jobs will have the same ScheduledStartDate. For example, if I set Job A to occur once a month starting from 25th May 09, all the jobs in the Job table will have the next ScheduledStartDate as 25th June 09. I really hope this all make sense cause I needed this recur function to be working.