MS Access search problem

[ManXY]

Problem is (I'll explain it by sample):
Three tables in database: one Books, second Persons, third Rents;
Rents contains data of renting books by Persons.
Form Rents contains two combo's Books and Persons for choose, and two dates (for rent) dateout and dateback.
How to check if book is out, during choosing a book in combo in Form.
I emphasize that there are three types of books:
-never rented until now(new or nonpopular books); CAN BE RENTED!
-rented once, twice, more times, but back in library; CAN BE RENTED!
-rented once, twice, more times, but now out of library; CANNOT BE RENTED!
Access has no similar command like SEEK in Clipper.
I try not to use check box for Books table, which tells if book is out or in library.
I try to solve this problem by query.

Anybody has solution for it??
Thanks in advance!
[sig][/sig]

 

[kathryn]

You can do this in a query. You would make the record source for the Book combobox a query or SQL statement. You need to build a select query on your Rent table, call it tblRent. You need to group by BookID and return the record with the Maximum date out. Then your criteria would be that DateOut<=Date() and DateIn is >=Date().

If you post your table names and field names I can get more specific. [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[ManXY]

Short explain (example):

Three tables in database:

Books:
IDbook
booksignature

Persons:
IDperson
Name
Surname

Rents:
IDrent
IDbook
IDperson
daterent
dateback

Now, Persons table is not interesting.

I make an SQL query and I put it into RowSource of ComboBox for
searching a books signatures which are now in library.

SELECT Books.IDbook, First(Books.booksignature) AS bookname
FROM Books LEFT JOIN Rents ON Books.IDbook=Rents.IDbook
GROUP BY Books.IDbook
HAVING ((Max(Rents.daterent)) Is Null Or Max(Rents.daterent)<Max(Rents.dateback))
ORDER BY First(Books.booksignature);

But I must put there one more condition i HAVING:
Exam: Or Books.IDbook=Rents.IDBook
But I got a problem there! I cannot mix conditions in this SQL like this.

Have you an answer for it?? [sig][/sig]