I have a very simple database that when someone does a search using my custom for it saves the following details to the database:
Username
Clientnumber
Date-time
On saving to the database the record gets a unique reference number.
The next time the access the list I want then to access the top 20 unique clientnumbers in the date/time order that they searched on them.
I've got it so that they pull back the top 20 unique record but struggling with getting them in Date order. My code is as follows:
I know that the SQL statment is messed up at the moment as I've been trying lots of different statements from when I was looking on google. any thoughts please?Private Sub GetPrev() Dim objMyConn As Object Dim objMyCmd As Object Dim objMyRecordset As Object Dim Username As String Username = Environ("UserName") ' Set the Variable with relevent code / data Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = New ADODB.Recordset objMyConn.ConnectionString = "Driver=SQL Server;Server=SQL\SQL;Database=SQL;Trusted_Connection=Yes;" objMyConn.Open Set objMyCmd.ActiveConnection = objMyConn objMyCmd.CommandText = "Select top 20 cs_cltnum from CSResults group by cs_cltnum order by max(cs_DT) where cs_user like '" & Username & "'" objMyCmd.CommandType = adCmdText objMyCmd.Execute Set objMyRecordset.ActiveConnection = objMyConn objMyRecordset.Open objMyCmd objMyRecordset.MoveFirst objMyRecordset.Sort "cs_DT Desc" With Me.cmbcltnum .Clear Do .AddItem objMyRecordset!cs_cltnum objMyRecordset.MoveNext Loop Until objMyRecordset.EOF End With objMyRecordset.Close objMyConn.Close Set objMyConn = Nothing Set objMyCmd = Nothing Set objMyRecordset = Nothing End Sub