I have used the following query and i think that has solved the problem.
sql = "patients"
Set rs = New ADODB.Recordset
rs.Open "select * from patients where date between #" & Format$(DTPicker1.Value, "mm/dd/yyyy") & "# AND #" & Format$(DTPicker2.Value, "mm/dd/yyyy") & "# ", Con, adOpenDynamic, adLockPessimistic
Dim X As Integer
Do While Not rs.EOF
X = X + rs.Fields(9).Value
DataReport1.Sections("Section5").Controls("label17").Caption = X
Set DataReport1.DataSource = rs