Hi all
I am on a final task for a tutorial I am doing about a Library which you may have already seem some posts for.
In this table I am creating I am listing customers with their:
Current total Overdue charge
and
Total Previous Charges
At the moment I have manage to get the table to display all the names once(that being only those with current overdue charges or previous charges) and then list all the charges underneath those names.
This is where my problem now lie. I struggling to work out how to total up those charges. So ideally I should have each person with only one row. The Current total overdue charge column should have a total figure in it or show as NILL and the Total Previous Charge should have a total figure in it or show NILL. But as I said currently I have each individual with all their figures listed. So each customer has several rows listed below them.
Hope this makes sense.
I am still fairly new to ASP and any help would be great. Many thanks in advance. Please see code below:
PHP Code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%'Displays Customers and theirs books with fine paid/owed and those that still have an overdue book to be displayed in red%>
<%'Has a search function%>
<%
Dim conn, connString, sqlSearchCustomerFine, rsSearchCustomerFine, searchCustomers, dueDate, diffDate, fineOwed, fineOwedTotal, checkedInDate, dueCheckDiffDate,customerName
connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("rltestingDB.mdb")
searchCustomers = Request.Form("searchCustomers")
if searchCustomers <> "" then
sqlSearchCustomerFine = "SELECT * FROM viewAllCustomersBooks WHERE DUE_DATE < DATE() AND NAME LIKE '%" & searchCustomers & "%'"
else
sqlSearchCustomerFine = "SELECT * FROM viewAllCustomersBooks WHERE DUE_DATE < DATE()"
end if
Set conn = Server.CreateObject("ADODB.Connection")
Set rsSearchCustomerFine = Server.CreateObject("ADODB.Recordset")
conn.Open(connString)
rsSearchCustomerFine.Open sqlSearchCustomerFine, conn
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Customer fine records</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="global.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
.style1 {color: #FF0000}
-->
</style>
</head>
<form method="post" action="personAccount.asp">
Search: <input type="text" name="searchCustomers" value="<%=searchCustomers%>" />
<input type="submit" value="Submit" />
</form>
<table width="800" border="0">
<tr>
<th>Name</th>
<th>Current total overdue charge</th>
<th>Total previous charges</th>
</tr>
<%customerName = ""%>
<%while(rsSearchCustomerFine.eof = false)%>
<%dueDate = rsSearchCustomerFine("DUE_DATE")%>
<%checkedInDate = rsSearchCustomerFine("CHECKED_IN_DATE")%>
<tr>
<%If rsSearchCustomerFine("NAME") <> customerName then%>
<td><%=rsSearchCustomerFine("NAME")%></td>
<%If dueDate < checkedInDate Then
diffDate = DateDiff("d",dueDate,checkedInDate)
fineOwed = diffdate*00.20%>
<td>Nill</td>
<td id="overDueAlertPaid"><%response.Write(FormatCurrency(fineOwed))%><%=(" Paid")%></td>
<%ElseIf (dueDate<Date()) or (checkedInDate = "") Then
diffDate = DateDiff("d",dueDate,Date())
fineOwed = diffdate*00.20%>
<td id="overDueAlert"><%= FormatCurrency(fineOwed) %></td>
<td>Nill</td>
<%End if%>
<%Else%>
<td> </td>
<%If dueDate < checkedInDate Then
diffDate = DateDiff("d",dueDate,checkedInDate)
fineOwed = diffdate*00.20%>
<td>Nill</td>
<td id="overDueAlertPaid"><%response.Write(FormatCurrency(fineOwed))%><%=(" Paid")%></td>
<%ElseIf (dueDate<Date()) or (checkedInDate = "") Then
diffDate = DateDiff("d",dueDate,Date())
fineOwed = diffdate*00.20%>
<td id="overDueAlert"><%= FormatCurrency(fineOwed) %></td>
<td>Nill</td>
<%End if%>
<%End if%>
<%customerName = rsSearchCustomerFine("NAME")%>
</tr>
<%
rsSearchCustomerFine.movenext
wend
%>
</table>
</body>
</html>
<%
rsSearchCustomerFine.close
Set rsSearchCustomerFine = Nothing
conn.close
Set conn = Nothing
%>
Adding the sum of fines up
