max date

[danab]

I want to open a recordset based on the latest date of a few records.
So far I have:
set rst = DB.OpenRecordset("select *from [prices] where [Company number]=" & company_number & " AND [product number] =" & product_num ).
I'm not sure how to fit in the clause that specifies the latest date.
Thanks for your time.
Danny.

[Lyla]

Hi there...

You could have a 3rd txtbox named say: Latest. In it you could enter the date you want to check. Also add a field to your tabel named Latest. So your SQL would be:

(select * from prices where Company number= company_number AND product number = product_num And latest >= #Latest# ).

G Luck

[danab]

I already have a field in my table that specifies the
date when the price was set .
the problem is after selcting my records via company number and product number I have a few records in my recordset (each with a differnt date of when I set the price for the perticular company) I want to get the record with the latest date with out knowing what the latest
is.
I am trying to do it in the sql statment.
Is there a way?
Thanks.
Dan.

[pavinda]

Let's try :
_____________________________
set rst = DB.OpenRecordset("select *from [prices] where [Company number]=" & company_number & " AND [product number] =" & product_num order by (yourdatefieldname) DESC).
You will get many record but the first record is the record that have latest date.