SQL for fld value problem

[TrollBro]

I'm trying to write an sql statement for an on_click event in a form that would check a table to find the record with the most recent record date, and assign certain field values from the record to variables so I can reference the variables in a comparison. I'm trying to make it a quick and efficient process, so have avoided dlookups. Am I even on the right track? I can't figure out how to assign the results from statement below to variables. Any ideas would help. Thanks

SELECT tblHist.FileDt AS LastFileDt, tblHist.FileName AS LastFileName FROM tblHist WHERE (((tblHist.FileName)=(Select [fileName] from tblHist where [FileDt] = (Select max([filedt]) from tblHist))));"

 

[PHV]

Take a look at the Recordset object (either DAO or ADODB).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TrollBro]

Thanks PHV

was hoping there was an simpler way than the DAO way, but I guess not.

 

[PHV]

Another way is an hidden ListBox with RowSource dynamically set to your SQL statement.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244