SQL Statement???

[Stevie]

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:

[Negative0]

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

[Stevie]

I know how to do it now :


SELECT COUNT(Work_Date), Work_Date
FROM Work_History GROUP BY Work_Date
ORDER BY COUNT(Work_Date) DESC


Cheers for the inout anyway. :)