Sum for textbox from SQL string having trouble

[GooGoocluster]

Trying to get a text box to display a sum but i cant get the code to work is there a better way. The text box is unbound the form only has a to date and from date boxes with this command button. I keeping gettting run time errors with this code. I copied it from another post.

 Dim SQLstr1 As String
Dim SQLstr2 As String
Dim rs1 As Recordset

SQLstr1 = "SELECT SUM(Pieces)AS HWsum FROM  dbo.[Sky Mail] Table_1 WHERE  ([Mail Date] BETWEEN CONVERT(DATETIME, '2005-09-05 00:00:00', 102) AND CONVERT(DATETIME, '2005-09-11 00:00:00', 102)) AND    (Account LIKE '7HW%')"
Set rs1 = CurrentDb.OpenRecordset(SQLstr1)

Me.Text6.ControlSource = rs1!HWsum

 

[PHV]

Have you tried the DSum function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886