Current status based on single date on multiple rows

Question

Hello all! I hope it's not a duplicate, but believe me, I've searched high and low.

I have a list of user records, let's call it qualifications. Example:

Row#. Date        User Qualification
1     2010-07-15  1    A1X
2     2010-08-30  1    B1X
3     2009-04-01  1    C1X

Now I'd like to do a query, where on a given date or date range, I get the user's qualification

I.e. a select for today would yield row 2 (B1X), a query for 2010-05-01 would yield row 3 (C1X)

I guess it boils down to the problem of doing a "BETWEEN" select on multiple single date rows.

Edit:

Found a solution for getting the user status on a single date:

SELECT s.* FROM status s WHERE s.Date=(SELECT MAX(s2.Date) FROM status s2 WHERE s2.DT<='(date)')

But how can I expand to a FROM-TO range?