updated - selecting MAX(date)

ok thanks for ur reply..
Let me go back..
The 2 tbles I am concerned with are: tblTransactions and tblExchangeRates

tblTransactions has:
transactID autoNumber
Date date of transaction
expCategory selected from a combo box on tblCategory
transCurrency selected from a combo box on tblLocation
transAmt cost of transaction
exchRate $amount from tblExchangeRate
AUD$ transAmt converted to AUD$ based on
exchRate


tblExchangeRate has:
exRateID autoNumber
exRateDate date of exchange rate
exAmount amount of exchange rate
exCurrency selected from tblLocation
(ie Rupee, Baht, US$...etc)

I want to create a form to add transactions (based on tblTransactions) and have the "exchRate" automatically entered based on the transaction Date.

The tblExchangeRate can be missing dates for a particular currency, so the transaction table needs to select the latest date in the tblExchangeRate to retrieve "exAmount" where the tlbTransaction.currency = tblExchangeRate.currency.

whew, ok, I hope I have explained that well enough.

Thanks again.