Return Overdue/NULL Records 1

[SBelyea]

Hey everyone -

I'm working with a stormwater facility management database (MS Access 2000 - we're a small town government) and I'm trying to create a query that will show me records where:

InspRequiredOn (date when the next inspection is) is < the current date

I think that I want to use Max(InspRequiredOn) < Date() (or whatever the SQL equivalent is for returning the current date) as my argument, but I am unsure how to implement this. I have already created a query that returns those properties which have upcoming inspections:

SELECT [tblOwner].[OwnersBusiness], [InspRequiredOn]+(([PropInspInterval]*365)+1) AS Expr1, [tblOwner].[AutoIDOwner], [tblOwner].[OwnersFirstName], [tblOwner].[OwnersLastName], [tblOwner].[OwnersStreetAdd], [tblOwner].[OwnersCity], [tblOwner].[OwnersState], [tblOwner].[OwnersZipCode], [tblOwner].[OwnersPhoneNumber], [tblOwner].[OwnersEmail], [tblOwner].[MntAgrSigned], [tblOwner].[MntAgrDate], [tblOwner].[MntAgrLoc], [tblOwner].[OwnerNotes]
FROM (tblOwner INNER JOIN tblProperty ON [tblOwner].[AutoIDOwner]=[tblProperty].[AutoIDOwner]) INNER JOIN tblInspection ON [tblProperty].[AutoIDProperty]=[tblInspection].[AutoIDProperty]
WHERE ((([InspRequiredOn]+(([PropInspInterval]*365)+1))>(Date()-1)))
ORDER BY [InspRequiredOn]+(([PropInspInterval]*365)+1);

If I need to post any more information, please let me know and I will be happy to oblige!

Appreciate everyone's help in advance!

- Stephen Belyea

 

[Golom]

Your code doesn't reflect your description.

It looks like [PropInspInterval] is the time in years between inspections and [InspRequiredOn] is the date of the last inspection.

If [InspRequiredOn] is really the date when the next inspection is required then

SELECT O.[OwnersBusiness], 
         [InspRequiredOn], 
       O.[AutoIDOwner], 
       O.[OwnersFirstName], 
       O.[OwnersLastName], 
       O.[OwnersStreetAdd], 
       O.[OwnersCity], 
       O.[OwnersState], 
       O.[OwnersZipCode], 
       O.[OwnersPhoneNumber], 
       O.[OwnersEmail], 
       O.[MntAgrSigned], 
       O.[MntAgrDate], 
       O.[MntAgrLoc], 
       O.[OwnerNotes]

FROM (tblOwner As O 
      INNER JOIN tblProperty As P   ON O.[AutoIDOwner]    = P.[AutoIDOwner]) 
      INNER JOIN tblInspection As I ON P.[AutoIDProperty] = I.[AutoIDProperty]

WHERE [InspRequiredOn] < Date() 

ORDER BY [InspRequiredOn]

 

[SBelyea]

I apologize - the code I provided does use the same fields, but it is used to calculate a future date based off of InspRequiredOn and then run a check on it to show future inspections.

Golom - the code you provided worked perfectly! I'm inexperienced with SQL, but I couldn't figure out how to properly write the query in Access' Query Design view. I also changed the WHERE line to

WHERE [InspRequiredOn] < Date() OR [InspRequiredOn] is NULL

so that I can see properties which haven't been inspected yet.

I really appreciate your help, and I apologize the confusion in the beginning! Thanks again!