PLEASE NOTE: THIS IS A RE-POST

how to retrieve a record that falls in a date range

I have 2 tables, one containing information about the staff of an office
(names, address, tel, etc.), and the other one containing their salary
history (StaffID, salary, date of salary modification). The 2 tables are
related by the field StaffID. Salaries for each staff are not fixed but can
be modified with time. For example, in Table Staffs, I have 2 persons, Mark
(ID=1) and Nelly (ID=2). In table Salary History, Mark ´s salary starts at
500 USD on 1-1-98, but on 1-6-99 has been increased to 600, and on 1-8-99,
modified again to 750 USD. Nelly, on the other hand, receives a salary of 650
USD as of 15-7-1998, but from 1-1-2000, this has been increased to 820.
Suppose we have to calculate their salaries for the month of July 1999.
Clearly we see that Mark´s salary for that month is of 600, and Nelly ´s is
750 (starting point), but I do not know how to create an expression that can
tell Access to retrieve this record by date comparison (July 1999 is greater
than 1-1-1998 and 1-6-1999, but smaller than 1-8-1999, so Access SHOULD
retrieve the record of 600 USD, corresponding to Mark ´s 1-6-99 ´s salary
modification, which is the most RECENT modification for that date range. The
same logic is for Nelly ´s salary). Please help me to give a solution to my
obstacle. Your valuable help would be much appreciated.

Luu Phuong Chi
Access beginner