Getting the total sum of the SQL query

[shabbaranks]

Hi Guys,

Ive got an SQL query as below, what Im trying to do is get the total value of that SQL query and drop it into a form text box.

The placing of the result on the form textbox isn't a problem but getting a sum total of the query result is proving to be a little tricky tricky tricky.

Dim strSQL As String
strSQL = "SELECT TestTable.Hours FROM TestTable" & _
" WHERE (((TestTable.sUser)=Forms!Submittedsheet_frm!AdminSelect_Combo.Column(0) AND" & _
"((TestTable.[Task Date])>=[Forms]![Submittedsheet_frm]![FromDateAdmin_TXTBox] And" & _
"(TestTable.[Task Date])<=[Forms]![Submittedsheet_frm]![ToDateAdmin_TXTBox];"

Thanks!

 

[pr2-eugin]

How about looping through the recordset to Sum the values?

Dim strSQL As String, rsObj As DAO.Recordset, dbObj As DAO.Database
Dim finalVal As Double

Set dbObj = CurrentDB

strSQL = "SELECT TestTable.Hours FROM TestTable" & _
         " WHERE (((TestTable.sUser) = '" & Forms!Submittedsheet_frm!AdminSelect_Combo.Column(0) & "' AND " & _
         "((TestTable.[Task Date]) >= #" & Format([Forms]![Submittedsheet_frm]![FromDateAdmin_TXTBox], "mm\/dd\/yyyy") & "# And" & _
         "(TestTable.[Task Date]) <= #" & Format([Forms]![Submittedsheet_frm]![ToDateAdmin_TXTBox], "mm\/dd\/yyyy") & "#;"

Set rsObj = dbObj.OpenRecordset(strSQL)
Do While Not rsObj.EOF
    finalVal = finalVal + rsObj.Fields(0)
    rsObj.MoveNext
Loop

theTextBoxName = finalVal
Set rsObj = Nothing
Set dbObj = Nothing

Or easier if you could Create this as a Saved Query then use DSum on it..

 

[shabbaranks]

Any idea what would cause a syntax error in query? The error 3075 shows the expression and it shows that the form values are correct for example the task dates resolve to the task dates in question from the form along with sUser

Thanks

 

[pr2-eugin]

Try debugging the Error using the Debug.Print strSQL just before the Set rsObj line, copy and paste the generated SQL string here..

What are the Data Type of the field sUser and Task Date?

 

[shabbaranks]

Thanks for your help on this. Here is the debug

SELECT TestTable.Hours FROM TestTable WHERE (((TestTable.sUser) = 'test.user' AND ((TestTable.[Task Date]) >= #02/09/2013# And(TestTable.[Task Date]) <= #06/09/2013#;

Data types are
sUser = Text
Task Date = Date/Time - short date

 

[pr2-eugin]

The problem seems to be uneven parentheses.. Try the following..

Dim strSQL As String, rsObj As DAO.Recordset, dbObj As DAO.Database
Dim finalVal As Double

Set dbObj = CurrentDB

strSQL = "SELECT TestTable.Hours FROM TestTable" & _
         " WHERE ((TestTable.sUser = '" & Forms!Submittedsheet_frm!AdminSelect_Combo.Column(0) & "') AND " & _
         "(TestTable.[Task Date] BETWEEN #" & Format([Forms]![Submittedsheet_frm]![FromDateAdmin_TXTBox], "mm\/dd\/yyyy") & "# And " & _
         "#" & Format([Forms]![Submittedsheet_frm]![ToDateAdmin_TXTBox], "mm\/dd\/yyyy") & "#));"

Set rsObj = dbObj.OpenRecordset(strSQL)
Do While Not rsObj.EOF
    finalVal = finalVal + rsObj.Fields(0)
    rsObj.MoveNext
Loop

theTextBoxName = finalVal
Set rsObj = Nothing
Set dbObj = Nothing

 

[shabbaranks]

The problem seems to be uneven parentheses.. Try the following..

Dim strSQL As String, rsObj As DAO.Recordset, dbObj As DAO.Database
Dim finalVal As Double
 
Set dbObj = CurrentDB
 
strSQL = "SELECT TestTable.Hours FROM TestTable" & _
         " WHERE ((TestTable.sUser = '" & Forms!Submittedsheet_frm!AdminSelect_Combo.Column(0) & "') AND " & _
         "(TestTable.[Task Date] BETWEEN #" & Format([Forms]![Submittedsheet_frm]![FromDateAdmin_TXTBox], "mm\/dd\/yyyy") & "# And " & _
         "#" & Format([Forms]![Submittedsheet_frm]![ToDateAdmin_TXTBox], "mm\/dd\/yyyy") & "#));"
 
Set rsObj = dbObj.OpenRecordset(strSQL)
Do While Not rsObj.EOF
    finalVal = finalVal + rsObj.Fields(0)
    rsObj.MoveNext
Loop
 
theTextBoxName = finalVal
Set rsObj = Nothing
Set dbObj = Nothing

You my friend are an absolute legend!!! Thank you

 

[pr2-eugin]

You are most welcome.