Hi Tony,
Welcome to Bytes!
You can create a query that includes all the fields from the pertinent tables and create expressions in the query that identify the date for the 6 week and 12 week cycle, and whether or not the inspection was completed by the date or not.
So, let's assume you have a field in a table called LastInspectionDate that identifies the date of the last completed (or current) inspection. Let's also assume that you have a field in the table called InspectionCompletionDate that identifies the date that the next inspection is completed. You would add both of these fields to the query.
First, create an expression that identifies the date 6 weeks from the last inspection date by using the following:
-
6Week: =DateAdd("ww", 6, [LastInspectionDate])
-
The expression above adds 6 intervals, which is weeks ("ww" is the setting for weeks) in this case, to the last inspection date.
Now, for the 12 week cycle, create another expression that is the same as the first one we just created, but change the number to 12:
-
12Week: =DateAdd("ww", 12, [LastInspectionDate])
-
Now you know the exact date for each cycle and we can create two more expressions that check to see if a date has been entered in the InspectionCompletionDate field (a date would indicate that the inspection was completed, whereas an empty field would indicate that the inspection has yet to be completed) and whether or not the current date has passed the due dates for the inspection. The expressions for these would be:
-
Missed6Week: IIf((IsNull([InspectionCompletionDate])) And (Date() > [6Week]), True, False))
-
and
-
Missed12Week: IIf((IsNull([InspectionCompletionDate])) And (Date() > [12Week], True, False))
-
If the value in the Missed6Week or Missed12Week is True, then the inspection hasn't been completed by the current date.
In the Criteria under the Missed6Week and Missed12Week expressions, you can type True and the query will only return those records that are past due. Actually, if you put both of these in the same query, you would probably want to type True on the Criteria line for Missed6Week and True on the Or line for the Missed12Week.
Hope this helps and good luck,
beacon
4
