 | I NEED ASP/ACCESS DATABASE HELP!!! |  |
|
dklbwf
|
|
 Posted: Thu Jan 08, 2009 9:00 pm |
|
 |
 |
|
|
Hello this is my first post and i hope i do it it right. I am a newbie to this ASP & Database work but have actually gotten to the point where i can dispaly everything from database. If you go to this link [url]www.huntsales.com/hscreports.asp[url] you will see that everything from database is there. What i am needing now is to know how i can have a user put in a start date and a end date and have the results compress the records and display only what the user has asked in the text box. PLEASE HELP!!! LOL.[/url]
|
|
|
|
jamie
HostMySite Sales Rep
| Joined: 19 Mar 2004 |
| Posts: 858 |
| Location: Newark, De |
|
|
| Posted: Thu Jan 08, 2009 10:09 pm |
|
|
|
|
|
What you want to do is a query that returns only specific values of the recordset. There is a nice article on that here:
http://www.plus2net.com/sql_tutorial/between-date.php
Note that this article is referring to SQL commands so it may not work exactly that way, but the theory is basically the same. |
|
|
|
dklbwf
|
|
| Posted: Mon Jan 12, 2009 2:09 pm |
|
|
|
|
|
Thanks So Much Jamie!!! I basically have the date range working now how i need it to, if you go to link www.huntsales.com/table.html and just hit search you will see resullts. What i need now is for it to calculate a users start time and finish time. Do you have any advice or links on where to start? Do i need to reformat and have the finish time in a whole different column maybe??
Thanks For Any Help |
|
|
 |
| | |
|
| | |
|
dklbwf
|
|
| Posted: Thu Jan 22, 2009 8:40 pm |
|
|
|
|
|
I've actaully got the program working great but just looking to get the break total on the right to show up. Currently i am getting all 0's....think you might be able to help with with this??? Just trying to get a total without break times included.
<style type="text/css">
<!--
.style1 {
font-size: 24px;
font-weight: bold;
}
body {
background-color: #FFFFFF;
}
body,td,th {
color: #000000;
}
a:link {
color: #FF0000;
}
a:visited {
color: #0066FF;
}
a:active {
color: #FF0000;
}
-->
</style>
<a href="index.html"><span class="style1">GO BACK</span></a>
<%
Public Function totals(x)
Dim lhour, lmin, lsec
lhour = 0
lmin = 0
lsec = CLng(x)
lhour = Fix(lsec / 3600)
lsec = lsec - (lhour * 3600)
lmin = Fix(lsec / 60)
lsec = lsec - (lmin * 60)
'format output as hh:mm
Dim tdec
tDec = CStr(lhour)
If Len(tdec) = 1 Then tdec = "0" & tdec
Dim adec
aDec = CStr(lmin)
If Len(adec) = 1 Then adec = "0" & adec
tdec = tdec & ":" & adec
If tdec = "0" Then tdec = "00:00"
totals = tdec
End Function
Dim cn, rs, sql, firstDate, lastDate, currentUser, breakStart, breakEnd, runningTotal, personTotal, breakSubTotal, breakTotal
firstDate = Request.Form("startDate")
lastDate = Request.Form("endDate")
Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
Set rsBreak = Server.CreateObject("ADODB.Recordset")
cn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Webclock.mdb")
sql = "SELECT DISTINCT start_table.USER_ID, start_table.LAST_UPDATED, end_table.LAST_UPDATED, start_table.STORE_ID, " & _
"start_table.DEPT_ID, start_table.PUNCH_DATE from Clockdata start_table " & _
"LEFT OUTER JOIN Clockdata end_table ON start_table.PUNCH_DATE = end_table.PUNCH_DATE AND start_table.USER_ID = end_table.USER_ID " & _
"WHERE start_table.TRAN_TYPE = 1 AND end_table.TRAN_TYPE = 6 AND (start_table.PUNCH_DATE between '" & firstdate & "' " & _
"AND '" & lastdate & "') AND end_table.TRAN_TYPE NOT IN (2,3) AND start_table.TRAN_TYPE NOT IN (2,3) "
IF Len(Trim(Request.Form("userid"))) > 0 Then sql = sql & "AND start_table.USER_ID = '" & Replace(Request.Form("userid"), "'", "''") & "'"
Set rs = cn.Execute(sql)
currentUser = rs.Fields(0)
runningTotal = 0
personTotal = 0
breakTotal = 0
If rs.EOF <> True AND rs.BOF <> True Then
Response.Write("<table border=""1""><tr><td>Employee</td><td>Store ID</td><td>Dept ID</td><td>Start Time</td><td>End Time</td><td>Working Time</td><td>Break Start</td><td>Break End</td><td>Total Less Breaks</td></tr>")
While NOT rs.EOF
Dim theName
sql = "select [Full Name] from Employee where user_id = '" & rs.Fields(0) & "'"
Set nameRS = Server.CreateObject("ADODB.Recordset")
Set nameRS = cn.Execute(sql)
If nameRS.EOF <> True AND nameRS.BOF <> True Then
theName = Trim(nameRS.Fields(0))
Else
theName = "UNKNOWN"
End If
nameRS.Close
If currentUser <> rs.Fields(0) Then
Response.Write("<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td><b>Sub Total:</b></td><td>" & totals(personTotal) & "</td><td>" & breakTotal & "</td></tr>")
Response.Write("<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>")
personTotal = 0
End If
Response.Write("<tr><td>" & theName & "</td>")
Response.Write("<td>" & rs.Fields(3) & "</td>")
Response.Write("<td>" & rs.Fields(4) & "</td>")
Response.Write("<td>" & rs.Fields(1) & "</td>")
Response.Write("<td>" & rs.Fields(2) & "</td>")
Response.Write("<td>")
If IsDate(rs.Fields(1)) AND IsDate(rs.Fields(2)) Then
Response.Write(totals(DateDiff("s", rs.Fields(1), rs.Fields(2))))
runningTotal = runningTotal + DateDiff("s", rs.Fields(1), rs.Fields(2))
personTotal = personTotal + DateDiff("s", rs.Fields(1), rs.Fields(2))
End If
Response.Write("</td>")
sql = "SELECT LAST_UPDATED from Clockdata where PUNCH_DATE = '" & rs.Fields(5) & "' AND " & _
"USER_ID = '" & rs.Fields(0) & "' AND TRAN_TYPE = 2"
Set rsBreak = cn.Execute(sql)
If rsBreak.EOF <> True AND rsBreak.BOF <> True Then
breakStart = rsBreak.Fields(0)
Else
breakStart = " "
End If
sql = "SELECT LAST_UPDATED from Clockdata where PUNCH_DATE = '" & rs.Fields(5) & "' AND " & _
"USER_ID = '" & rs.Fields(0) & "' AND TRAN_TYPE = 3"
Set rsBreak = cn.Execute(sql)
If rsBreak.EOF <> True AND rsBreak.BOF <> True Then
breakEnd = rsBreak.Fields(0)
Else
breakEnd = " "
End If
Response.Write("<td>" & breakStart & "</td>")
Response.Write("<td>" & breakEnd & "</td>")
Response.Write("<td>")
If IsDate(breakStart) AND IsDate(breakEnd) AND IsDate(rs.Fields(1)) AND IsDate(rs.Fields(2)) Then
Response.Write(totals(DateDiff("s", rs.Fields(1), rs.Fields(2)) - DateDiff("s", rs.Fields(1), rs.Fields(2))))
End If
Response.Write("</td>")
Response.Write("</tr>")
currentUser = rs.Fields(0)
rs.MoveNext
Wend
Response.Write("<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td><b>Sub Total:</b></td><td>" & totals(personTotal) & "</td>")
Response.Write("</tr>")
rsBreak.Close
Response.Write("<tr><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td><b>Total:</b></td><td>" & totals(runningTotal) & "</td></tr>")
Response.Write("</table>")
Else
Response.Write("No records returned!!!")
End If
rs.Close
cn.Close
%>
|
Thanks for any help Sir!!! |
|
|
|
| | |
|
Josh
Forum Regular
| Joined: 01 Apr 2004 |
| Posts: 1047 |
| Location: Felton, Delaware |
|
|
| Posted: Thu Jan 22, 2009 9:44 pm |
|
|
|
|
|
What type of fields are the fields that you're storing the date and time? Are they Date/Time fields? Or varchar? Text?
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
All times are GMT
Page 1 of 1
|
|
|
|
|
