Hello,

I like to query based on some fields to generate a report: Date range, Department, Student with date range. I have the form to work on searching....

However, I need to calculate totals for each of these fields from a report.
For example: if I search for students and the search results are:
Department Date Range Student Cost
-------------------------------------------------------
DeptA 1/1/2012-12/31/2012 StuA $100
DeptA 1/1/2012-12/31/2012 StuB $50
DeptA 1/1/2012-12/31/2012 StuC $50

How can I calculate the total of cost automatically online (= $200)?
Thanks.

Here is my code:
Code:
<%

Path = Request.ServerVariables("PATH_TRANSLATED")
                While (Right(Path, 1) <> "\" And Len(Path) <> 0)
                                iLen = Len(Path) - 1
                                Path = Left(Path, iLen)
                Wend

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.open strCon
                SelectStmt = "Select * From view_costs Where "
                WhereClause = ""
                WhereBetweenClause = "BETWEEN"
                 If Request("qryDepartment") <> "All Departments" Then
                               qryDepartment = replace(request("qryDepartment"),"'","''")
                                WhereClause = WhereClause & "Department = '" & qryDepartment & "' AND "
                End If
     If Request("qryStudents") <> "All Students" Then
                                WhereClause = WhereClause & "Name = '" & Request("qryStudents") & "' AND "
                End If
                               

sStartDate = Request("StartDate")
sEndDate = Request("EndDate")
If IsDate(sStartDate) And IsDate(sEndDate) Then
                WhereClause = WhereClause & "(StartDate >= '" & sStartDate & "' AND EndDate <= '" & sEndDate & "') "
End If


Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.open strCon
                pstart = trim(request.form("pstart"))
                pfinish = trim(request.form("pfinish"))
                Set getDepartment = Server.CreateObject("ADODB.Recordset")
                getDepartment.Open "Select Department from view_costs order by Department;",adoCon
                 Set getname = Server.CreateObject("ADODB.Recordset")
                getname.Open "Select Name from view_costs order by Name;",adoCon
%>

<html>
<head>
<title>The Resport</title>
</head>

<body>
<table width="770" align="center">
                <tr>
                                <td colspan="4" class="n"><span id="h3">Search Menu</span></td>
                </tr>
                <form method=Department action="Search.asp" name="Search">
                                                <tr>
                <td>

 <%          if not getDepartment.eof then %>
    <select name="qryDepartment" size="1" title="Select a Department Name" style="width:200;background-color=#F5D0A9;">
    <option selected>All Departments</option>
   <%          do until getDepartment.eof %>
<option><%= getDepartment("Department") %></option>
 <%          getDepartment.MoveNext
loop       %>
</select>
  <%          end if %>
                </td>
                <td>
<%          if not getname.eof then %>
<select name="qryStudents" size="1" title="Select a Student Name" style="width:200;background-color=#F5D0A9;">
 <option selected>All Students</option>
 <%          do until getname.eof %>
<option><%= getname("Name") %></option>
 <%          getname.MoveNext
loop       %>
</select>
 <%          end if %>
                </td>
<%

%>

                                                <td><span id="b">&nbsp;Start:</span> <input name="StartDate" type="text" size="15" maxlength="12" value="<%=sStartDate%>">

                                                <a href="javascript:;" onClick="displayDatePicker('StartDate', this);"><img src="calendar.gif"  alt="calendar"></a></td>

                                                <td><span id="b">&nbsp; End:</span> &nbsp;<input name="EndDate" type="text" size="15" maxlength="12" value="<%=sEndDate%>">

                                                <a href="javascript:;" onClick="displayDatePicker('EndDate', this);"><img src="calendar.gif" alt="calendar"></a></td>                          

                                </tr>

 

    <tr class="search-bg">

                                <td colspan="6">

            <input type="button" name="Submit" value="Search" onClick="if (isDate()) document.Search.submit();">

</tr></table>

 

 

<% If oRs.RecordCount = 0 Then %>

<p></p>

<% Else %>

<table width="960" align="center">

 

                <form method="Department">

        <tr bgcolor="#FE9A2E" height="25">

          <td class="a">&nbsp;Department Name</td>

          <td class="a">&nbsp;Student</td>

          <td class="a">&nbsp;Start</td>

          <td class="a">&nbsp;End</td>

                                  <td class="a">&nbsp;Cost</td>

         </tr>

        <tr>

          <td><%=oRs("Department")%>&nbsp;</td>

          <td><%=oRs("Name")%>&nbsp;</td>

                                  <td><%=FormatDateTime(Month(oRs("StartDate")) & "/" & Day(oRs("StartDate")) & "/" & Year(oRs("StartDate")))%>&nbsp;</td>

          <td><%=FormatDateTime(Month(oRs("EndDate")) & "/" & Day(oRs("EndDate")) & "/" & Year(oRs("EndDate")))%>&nbsp;</td>

            <td <%=sRowStyle%>>$<%=oRs("Cost")%>&nbsp;</td>

 

                    </tr>

                                  <% oRs.MoveNext %>

                                  <% WEND %>

    </table>

                                </td>

                </tr>

  </table>

 

<%          oRs.close

                set oRs = nothing

                set adoCon = nothing

%>