i put the SQL in a query, so you must make or change the query name: qsGetLatestData
Code:
Public Sub BuildSql()
Dim tdf As TableDef
Dim qdf As QueryDef
Dim sSql As String, sFld As String
Set tdf = CurrentDb.TableDefs("exchange")
'get the last field name (most recent date)
sFld = tdf.Fields(tdf.Fields.Count - 1).Name
'use field in query
sSql = "SELECT [Field1], [" & sFld & "] FROM [exchange] WHERE [Field1]='Us dollar'"
'save sql as the query
Set qdf = CurrentDb.QueryDefs("qsGetLatestData")
qdf.SQL = sSql
qdf.Close
DoCmd.OpenQuery qdf.Name 'open the query
Set tdf = Nothing
Set qdf = Nothing
End Sub