Unanswered: DLOOKUP to display Value = Smallest (earliest) Autonumber
I'm trying to use DLOOKUP in a subreport to return a Start Balance or in other words the "Balance" value of a query that matches lowest (earliest) "Transaction Number". The subreport uses a query that looks like this:
Date Balance Tran Number
1/7/08 12.35 123
1/7/08 14.97 124
2/7/08 35.96 125
2/7/08 76.98 126
2/7/08 98.76 127
It uses the same date criterior as the main report so the start balance is simply the earliest entry and the end balance is the most recent. Trans Number 122 isn't shown for example because it occurs before the 1st July and is outside the critrior of the main report. So in the above example Start Balance = Tran Number "123" = 12.35.
My problem is I've no idea how to get DLOOKUP to do this. My guess would be something like:
=DLookUp("[Balance]","[BALANCE]","[TRANNUMBER] < [TRANNUMBER]"