Forum Discussion
mok1995
Jun 30, 2021Copper Contributor
date increasing
Hello all I have an excel sheet containing information for many machines that needs maintenance, each machine has a starting date of maintenance and the maintenance will be done repetitively in a sp...
George_Hepworth
Jun 30, 2021Silver Contributor
There are two parts to the question.
First, filtering on a specific date to see which machines are due for maintenance.
Second, advancing the dates to the present.
From Access, you can link the excel worksheet and use it similarly to how you would a native table, albeit with some limitations. Excel, for example, can't be updated directly from Access, so any new data entries or updates will need to be done on the Excel side. You could resort to VBA to use recordsets to update the workbook, I think, although that's probably more involved than you want.
Write a query in Access, using this linked Excel worksheet, and apply a criteria to the field containing the maintenance date you want to filter on. In essence it would be:
SELECT *
FROM YourLinkedExcelWorkSheet
WHERE MaintenanceDate = Today()
Noting, however, that you have different schedules for different machines, you'll need to adopt a more complex evaluation. In order to work that out, we'd have to have a full explanation of each of your codes in the column called Tasks.Schedule, though.
Advancing the dates is a matter of updating the value in Tasks.DateLastDone as appropriate. How do you do that now?