Struggling with date in query

[cmrhema]

I have a table called as DividendDetails
It consists of columns dividend, rateofinterest, dateofinterest, depositbankname ,fdno,depositac countno and chequeno

eg I have the values corresponding to the colums as below
Col1. 10,10,2/2/2006,AA,1010,10 ,10
Col2 20,20,3/3/2007,AA,1010,20 ,20

In this way there will be many different fdno(fixed deposit no) whose dividend will be stored in the above manner

WHAT I WANT is If i choose a particular fdno then I should be able to get all details pertaining to the last dateofinterest
If I choose fdno 1010 as in the above example I should able to view the col2 details as it is the latest.
I am able to design the first part which is extremely easy

MyRs2.Open "select * from dividenddetails where fdno='" & Trim(cbFDADD.Te xt) & "'", MyCon, adOpenDynamic, adLockOptimisti c

But how shall I put forth the date part
I tried max(dateofinter est). It produced plenty of errors

Any help
Thanks in advance

[cmrhema]

Got the answer
Its this way


select * from dividenddetails where fdno=5789 and dateofinterest= (select max(dateofinter est)
from dividenddetails where fdno=5789)

where 5789 is some valu of fdno