Dates & Future Dates

[don]

Hi I'm using Access 2003 and have got a maintenance database which I
would like to be able to flag up when the weekly, monthly, quarterly
maintenance tasks are due based on a start date. Can access generate
a table of dates based on the start date that I can use to refer to
for each asset maintained?

Many thanks for your help

DonH

 

[Jeff Boyce]

Rather than generating "a table of dates" (which would need to be changed
every time there was a change in a start date), how about using an Access
query? By using a query, any time you need to know the "weekly, monthly,
quarterly maintenance tasks ... due" you can have a real-time list.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[don]

Rather than generating "a table of dates" (which would need to be changed
every time there was a change in a start date), how about using an Access
query?  By using a query, any time you need to know the "weekly, monthly,
quarterly maintenance tasks ... due" you can have a real-time list.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentionedin
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff,

Thanks for reply. I've been trying to do that but can't get my head
around it. I had tried using a start date with a start date plus 7
column in query to give me a date a week on as a start but that
creates a new column date not a new date in my start date, can you
please expand on your suggestion.

Many thanks

DonH

 

[rbeach]

Don,

If you have a table with with the items to be maintained and the maintenence
completed date as below you could create the two following queries. This
would give a list of all of the next maintenance dates for each item:

MaintTable:
MaintDate Item
11/1/2009 Belt
11/2/2009 Drive
10/1/2009 Belt

LastMaint:
SELECT MaintTable.Item, Max(MaintTable.MaintDate) AS MaxOfMaintDate
FROM MaintTable
GROUP BY MaintTable.Item;

NextMaint:
SELECT LastMaint.Item, LastMaint.MaxOfMaintDate AS LastMaint,
IIf([Item]="Belt",[LastMaint]+7,[LastMaint]+30) AS NextMaint
FROM LastMaint;

Hope this helps.