Using record in another field as Criteria in a SQL Statement 1

[racerboy]

I have a report based on this SQL statement:
SELECT Information.[Inspection Number], DateDiff("y",[PendSent],Now()) AS DaysElapsed
FROM Information
WHERE (((DateDiff("y",[PendSent],Now()))>15));

Basically, it tells me how many days have passed since I sent out a pending report (PendSent field) Recipients have a 15 day grace period. I have another field called PendRcvd (date the pending report was received back). What I want my SQL statement to do is run the query again, but not list any reports that have been entered as 'returned'. Does this make sense?

E.g., I sent out a pending on 03/01/01. Starting on 03/16/01, every time I run the query, it gives me the report name and the number of days outstanding. Let's say I get the report back on 03/17/01. If I run the query that day, it will tell me that the report is 2 days outstanding. But all I really want is to know how many of the reports are still outstanding. I want to have another query that will generate a report of all the reports that were outstanding (this will take the difference between date sent and date received, but that's a query for another day). So what I want to know is how to add additional criteria (I already have the >15 criteria) that will look to see if there is an entry in the PendRcvd field, and if so, not report that report as outstanding.

Thanks for any help you might be able to provide!

 

[RickSpr]

I assume PendRcvd is null if the report hasn't come back. Simply add "AND PendRcvd IS NULL" to your criteria string. Rick Sprague

 

[racerboy]

Rick,
Thanks so much! Here's how I incorporated your suggestion, and it worked out beautifully!

WHERE (((DateDiff("y",[PendSent],Now()))>15) AND ([PendRcvd] Is Null));

I am really new to Access and SQL and you are going to see a lot of me around these parts. )

Thanks again!

f.