Finding a date in a table

[Guest]

I have a form that assigns dates to naes that show in a combo box. This info
is stored as records in a table. Later, in another procedure, I use
DLookup() to see if a particular name is in the table. The problem is this.
A given name might appear several times, in the table, with different dates.
How could I find the most recent date for a name in this table?

Nick

 

[Guest]

Here is a function you can use to get the most recent name....


Function GetMostRecentDate(strName As String) As Date

Dim strSQL As String
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strTemp As Strin
'**********************************************************************************************
strSQL = "SELECT Max([DATE_FIELD_NAME]) "
strSQL = strSQL & " From TABLE_NAME "
strSQL = strSQL & " WHERE [NAME] = " & Chr(34) & strName & Chr(34) & ";"

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

Set cnn = Application.CurrentProject.Connection

rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic

strTemp = rst.Fields(0).Value

rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing

GetMostRecentDate = strTemp

End Function

You're going to have to edit the SQL statement to supply the three items not
stated in your question, namely:

1) DATE_FIELD_NAME
2) TABLE_NAME
3) FIELD_NAME