Searching through Recordset

[bcooler]

Hello-

I have a need to look up the record number (auto-numbered primary key) of the earliest (and latest) record in my recordset. I've been stuck trying to use DMIN and DMAX, but I find it does not accomplish what I want.

As best I can tell, the field that it uses to search on is the only field returned. I want to search on the date field and THEN return the record number.

To accomplish this I had to use this really nasty code here, which works, but I've got to know there's a cleaner way. Any ideas?

EarliestEntry = DLookup("[Date_ID]", "tblTravelInfo", "[Expense_ID] = " & Forms!frmExpense.Form.txtExpense_ID & " AND ExpenseDate = #" & DMin("ExpenseDate", "tblTravelInfo", "[Expense_ID] = " & Forms!frmExpense.Form.txtExpense_ID) & "#")

Thanks!
Brian

 

[dhookom]

I would attempt to use the faq701-4233 function with the expression (assumes Expense_ID is numeric):

EarliestEntry = Concatenate("SELECT TOP 1 Date_ID FROM tblTravelInfo WHERE Expense_ID = " & Forms!frmExpense.Form.txtExpense_ID & " ORDER BY ExpenseDate ")

You may need to add another unique field to the ORDER BY clause so you don't risk returning more than one record. Also, I think you can use Forms!frmExpense.txtExpense_ID rather than Forms!frmExpense.Form.txtExpense_ID.


Duane
Hook'D on Access
MS Access MVP