I have a table called Work_History and in it a field called Work_Date.
How can I get hold of the Work_Date which occurs most often? :confused:
Printable View
I have a table called Work_History and in it a field called Work_Date.
How can I get hold of the Work_Date which occurs most often? :confused:
Try this code on for size:
-------------------------------------------------------
Dim CurrentDate As Date
Const StartDate = "01/01/1997"
Dim EndDate As Date
Dim rs As Recordset
Dim MaxCount As Integer
Dim MaxDate As Date
MaxCount = 0
EndDate = Date
For CurrentDate = StartDate To EndDate
csql = "SELECT count(Work_Date) as expcount FROM Work_History where Work_Date ='" & CurrentDate & "'"
On Error Resume Next
rs.Close
On Error GoTo 0
rs.Open csql, Database
If rs.EOF = False Then
If rs.Fields("expcount") > MaxCount Then
MaxDate = CurrentDate
MaxCount = rs.Fields("expcount")
End If
End If
Next
-------------------------------------------------------
Hope this helps
I know how to do it now :
Cheers for the inout anyway. :)Code:SELECT COUNT(Work_Date), Work_Date
FROM Work_History GROUP BY Work_Date
ORDER BY COUNT(Work_Date) DESC