Selecting Date range and Calculate a sum

[bhashithe]

Hello guys,
I have came into a bit of an annoying problem, As i know what i am trying should work properly. This is the problem, I want to select a date range from "Production" table where it agrees to the Dept_ID too. And then calculate the summation of the columns "hours", "produced" & "waste" of that particular range selection.
This is my code:

Option Compare Database

Private Sub cmdCal_Click()
Dim sql As String
Dim rs As Recordset
Dim qdef As DAO.QueryDef
Dim hours, waste, produced As Integer
hours = 0
waste = 0
produced = 0

sql = "SELECT * FROM Production WHERE Dept_ID = " & cmbDept.Value & " AND Pro_Date >= " & txtFrom.Value & " And Pro_Date <= " & txtTo.Value & " "

On Error GoTo errorHandler:
Set rs = CurrentDb.OpenRecordset(sql)

Do Until rs.EOF
    hours = hours + rs(2)
    produced = produced + rs(3)
    waste = waste + rs(4)
        
    rs.MoveNext
Loop
txtPro.Value = produced
txtHr.Value = hours
txtWaste.Value = waste
Exit Sub

errorHandler:
txtPro.Value = produced
txtHr.Value = hours
txtWaste.Value = waste

End Sub

But it returns nothing, When i remove the errorHandler, it says that no records were found.
What have i mistaken? Please I am desperate now.
Thanks In advance,
Bhashithe

 

[Isskint]

hi bhashithe

Is Pro_Date actually stored as a Date? If so then you need to use # DATE #, so;

Pro_Date >= #" & txtFrom.Value & "# And Pro_Date <= #" & txtTo.Value & "#;"

Also you should finish an SQL string with a semi colon

 

[bhashithe]

I would thank you a million times if i can press that button again and again!!
That worked like a charm!!!