Query Criteria (1 Viewer)

[Eyeswideopen11]

Hi,
I have a query that shows all the books that have been borrowed. I created a form to show all these events they include Date Out Date Returned whether the book is out or available. I need to be able to add new borrowing events to this form, but I need a way to restrict it based on whether the book is out or available.

Thanks,

 

[plog]

Let's call your existing table Loans and assume you have a table of all books called Books.

1. Query: LoanedOut - Based on Loans, it will show BookID and for criteria it will use DateOut is Null.

2. Query: Available - Based on Books and LoanedOut (the query above). Link them BookId to Bookid and then change the JOIN link to show all records from Books and just those matching from LoanedOut. You will show the BookID and BookName from Books and for criteria you will use BookID from LoanedOut is Null.

Avialable can now be used on your CheckOut Form to show just the books available to be checked out. You would most likely use this as the source of a combo box on a form who's record source is Loans. That way when you enter a new record into Loans the drop down limits you to only seeing books that are available.