Date Compare in Access db

[neelaseenu]

Hi there
Can some one help me with this issue. I have 2 text fields which user selects the date from calender control. Now i want to retreive data from table which falls in the range of these 2 text fields
here is the code i have written. When i run this form it returns the resultset with no rows.(EMPTY resultset)

sdate = CDate(Forms![ResolvedIncidentsbySevierity]![Text3])
edate = CDate(Forms![ResolvedIncidentsbySevierity]![Text5])
 
sql = "select IncidentsResolved.Severity,count(IncidentsResolved .Severity) from IncidentsResolved WHERE ((IncidentsResolved.[Arrival Time] >= " & sdate & ") AND (IncidentsResolved.[Arrival Time] <= " & edate & ")) GROUP BY IncidentsResolved.Severity"
 
Set MyDB = DBEngine.Workspaces(0).OpenDatabase("D:\Reports\db 1.mdb")
Set MySet = MyDB.OpenRecordset(sql, DB_OPEN_DYNASET)
 
If (MySet.EOF = False And MySet.BOF = False) Then
MySet.MoveFirst
level1.Value = MySet.Fields(1)
MySet.MoveNext
level2.Value = MySet.Fields(1)
MySet.MoveNext
level3.Value = MySet.Fields(1)
MySet.MoveNext
' level4.Value = MySet.Fields(1)
Else
MsgBox ("No data found")
End If
 
MySet.Close
Set MyDB = Nothing

I have also tried by replacing the above sql stmt to and still no luck

sql = "select IncidentsResolved.Severity,count(IncidentsResolved .Severity) from IncidentsResolved WHERE ((IncidentsResolved.[Arrival Time] >= #sdate#) AND (IncidentsResolved.[Arrival Time] <= #edate#)) GROUP BY IncidentsResolved.Severity"

[pdebaets]

Try this:

sql = "select IncidentsResolved.Severity,count(IncidentsResolved .Severity) from IncidentsResolved WHERE ((IncidentsResolved.[Arrival Time] >= #" & sdate & "#) AND (IncidentsResolved.[Arrival Time] <= #" & edate & "#)) GROUP BY IncidentsResolved.Severity"

[NeoPa]

Try :

sql = "SELECT [Severity]," & _
              "Count([Severity]) " & _
      "FROM IncidentsResolved " & _
      "WHERE [Arrival Time] Between #" & Format(Me.sdate,'m/d/yyyy') & "# " & _
                               "And #" & Format(Me.edate,'m/d/yyyy') & "# " & _
      "GROUP BY [Severity]"

PS. May I remind you that [ CODE ] tags are expected whenever posting code in these forums.

Welcome to Bytes :)