Reoccurring Events - monthly, weekly

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a Maintenance Work Order Database, and they would like it to
create a new work order automatically for preventive maintenace (PMs). These
PMs are due weekly and monthly. I have created the PM records per machine in
a seperate table, and I would like it to append to the work order table on a
specified day of each month. I have attempting using wild cards for month and
year in the due_date, and then say when say when due_date = Now(), execute,
but I guess the wild cards are not correct, because the date formatted field
does not accept it.

So, I either need to know what are the correct date wild cards or a
different approach to accomplish the same thing. Please help.

Thanks,
Gene
 
If you don't care about the day of the week that the reminder appears on the
report, you can just enter a number X between 1 and 28 for each PM and use
the criteria Day(Date()) = X for the report.
 
I have created a Maintenance Work Order Database, and they would like it to
create a new work order automatically for preventive maintenace (PMs). These
PMs are due weekly and monthly. I have created the PM records per machine in
a seperate table, and I would like it to append to the work order table on a
specified day of each month. I have attempting using wild cards for month and
year in the due_date, and then say when say when due_date = Now(), execute,
but I guess the wild cards are not correct, because the date formatted field
does not accept it.

So, I either need to know what are the correct date wild cards or a
different approach to accomplish the same thing. Please help.
Click to expand...

A Date is not a String, so wildcards won't work. A date/time value is
stored internally as a Double Float number, a count of days and
fractions of a day (times) since midnight, December 30, 1899.

I'm guessing that you'll want to use an Append query, selecting the
most recent PM and using the DateAdd() function to add one month (or
one week) to the most recent pm date. What's the structure of your
workorder table? How and when do you want these new records added?

John W. Vinson[MVP]
 
Thanks for the reponse. I am not sure how to answer the first question about
the work order table structure, but it has all the fields neccessary to
submit a work order, once the person completes the form, it appends the work
order table, and adds a date and time stamp (long date format) and sends an
email with a work order report attached to the maintenance department. Half
the fields are completed by the requestor and the rest are completed by the
Maint. department, when they close out the work order. I figured I would have
a table with all the reoccurring PMs, which are weekly, monthly and annually
and when its time of week, month or year comes, it would append to the work
order table and send the notice to the Maint. Dept.
 
Thanks for the reponse. I am not sure how to answer the first question about
the work order table structure
Click to expand...

Please post the names and datatypes of the relevant fields - the
Primary Key, the date field, and any other fields that need to be
appended to create a new work order. Knowing *what your database is
designed to accomplish* does indeed help; thank you; but knowing how
the database is constructed is also necessary to give you a good
answer.

John W. Vinson[MVP]
 
Below are the the two table's fields, the first WO_Table, is where all work
orders go, the second table PM_Schedule_Table, has the fields I need to
append to the WO table, which is a work in progress, there is no primary key
defined yet.

WO_Table
ID (Auto number, primary key)
Open Date (Date/Time - general date)
Machine_Number(text)
Machine_Desc (text)
Requestor (text)
Dept (text)
Downtime_Code (text)
Problem (memo)
Corrective_Action (memo)
Maint_Associate (text)
Downtime (number)
Status (text)
Critical_Machinery (Yes/No)
Closed_Date (Date/time - general date)

PM_Schedule_Table
Open_Date
Machine_Number
Machine_Desc
Requestor_Dept
Downtime_Code
Maint_Associate
 
SQL server has job scheduling

Maybe if you’re not already using an ADP you might consider upsizing. Then you could use the jobs in SQL server to schedule and lunch your reports.
 
Back
Top