|
| |
|
|
jgeatty
Posts: 199
Joined: 10/14/2004
Status: offline
 |
View records newer than 7 days - 2/7/2007 14:42:31
This is probably a simple question that I'm overthinking, but I am trying to display records that have been modified in the database within the last 7 days. Here is my current SQL code from the DRW which works great, I just want to add: AND iLastModified>="Today's date"-"7 days"
SELECT * FROM MainDataRespository WHERE OwnerFullName LIKE '%::OwnerFullName::%' ORDER BY TicketNo DESC
Thanks.
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: View records newer than 7 days - 2/7/2007 14:59:55
quote:
SELECT * FROM MainDataRespository WHERE OwnerFullName LIKE '%::OwnerFullName::%' ORDER BY TicketNo DESC
If it is an Access DB and the field is a Date/Time field, try this:
SELECT * FROM MainDataRespository WHERE (OwnerFullName LIKE '%::OwnerFullName::%') AND (LastModified >= "#" & DateAdd(d,-7,Date()) & "#) ORDER BY TicketNo DESC
Hope it helps.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jgeatty
Posts: 199
Joined: 10/14/2004
Status: offline
|
RE: View records newer than 7 days - 2/7/2007 16:09:25
At first there was a problem with the double quotes so I changed them to single quotes and then got this error:
Database Results Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]'Date' is not a recognized function name.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
One or more form fields were empty. You should provide default values for all form fields that are used in the query.
| |
|
|
|
jgeatty
Posts: 199
Joined: 10/14/2004
Status: offline
|
RE: View records newer than 7 days - 2/7/2007 16:27:27
It's a SQL database... here's my code for the page:
<body>
<form name="sort" method="POST" action="test.asp?OwnerFullName=<%=Request("OwnerFullName")%>">
<table border="0" width="100%" cellpadding="0" cellspacing="0">
<tr>
<td width="100%" valign="top" height="22"><b>Select an Owner and click View</b></td>
</tr>
<tr>
<td width="100%"><b>Sort by Owner:</b> <nobr>
<!--#include file="../_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT DISTINCT OwnerFullName FROM MainDataRespository ORDER BY OwnerFullName ASC"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="remedy"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="OwnerFullName"
fp_sMenuValue="OwnerFullName"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<select NAME="OwnerFullName" SIZE="1">
<option selected value="">All Owners</option>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<option><%=FP_FieldHTML(fp_rs,"OwnerFullName")%></option>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</select>
<input type="submit" value="View" name="B1"> <a href="reports.asp" target="_blank">View
Reports</a>
</nobr>
</td>
</tr>
</table>
</form>
<!--#include file="../_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT COUNT(*) AS myCount FROM MainDataRespository WHERE OwnerFullName LIKE '%::OwnerFullName::%' AND Status<'6' AND (iLastModified >= '#' & DateAdd(d,-7,Date()) & '#)"
fp_sDefault="OwnerFullName="
fp_sNoRecords="No records returned."
fp_sDataConn="remedy"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<p><b>Total RFC's/SR's</b> = <%=FP_FieldVal(fp_rs,"myCount")%></p>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
<table border="1" bordercolor="#999999" cellspacing="0" width="2100">
<thead>
<tr>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Ticket No.</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Priority</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Request<br>Type</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Create<br>Date</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Requestor</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Owner</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Summary</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Status</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Planned<br>Start Date</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Planned<br>End Date</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Actual<br>Start Date</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Actual<br>End Date</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">CTI's</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Maker</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Checker</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Verifier</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Work Log</font></b></td>
<td bgcolor="#3399FF" align="center"><b><font color="#FFFFFF">Add'l Info Tab</font></b></td>
</tr>
</thead>
<tbody>
<!--#include file="../_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT * FROM MainDataRespository WHERE OwnerFullName LIKE '%::OwnerFullName::%' AND Status<'6' AND (iLastModified >= '#' & DateAdd(d,-7,Date()) & '#) ORDER BY TicketNo DESC"
fp_sDefault="OwnerFullName="
fp_sNoRecords="<tr><td colspan=11 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="remedy"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=100
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=18
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
<td <%
Dim x, bgcolor
if x = 1 then
bgcolor="silver"
response.write "bgcolor='silver'"
x=2
Else
bgcolor="White"
Response.write "bgcolor='white'"
x=1
End if %>><%=FP_FieldVal(fp_rs,"TicketNo")%></td>
<td bgcolor='<%=bgcolor%>' align="center"><% IF FP_FieldVal(fp_rs,"Priority")="0 " THEN Response.Write("Low") ELSE IF FP_FieldVal(fp_rs,"Priority")="1 " THEN Response.Write("Medium") ELSE IF FP_FieldVal(fp_rs,"Priority")="2 " THEN Response.Write("High") ELSE IF FP_FieldVal(fp_rs,"Priority")="3 " THEN Response.Write("Critical") ELSE Response.Write(" ") END IF %></td>
<td bgcolor='<%=bgcolor%>' align="center"><% IF FP_FieldVal(fp_rs,"RequestType")="0 " THEN Response.Write("RFC") ELSE IF FP_FieldVal(fp_rs,"RequestType")="1 " THEN Response.Write("SR") ELSE Response.Write(" ") END IF %></td>
<td bgcolor='<%=bgcolor%>'><%=FP_FieldVal(fp_rs,"CreateDate")%></td>
<td bgcolor='<%=bgcolor%>'><%=FP_FieldVal(fp_rs,"RequesterFullName")%><br><%=FP_FieldVal(fp_rs,"RequesterPhoneNo")%></td>
<td bgcolor='<%=bgcolor%>'><%=FP_FieldVal(fp_rs,"OwnerFullName")%></td>
<td bgcolor='<%=bgcolor%>'><%=FP_FieldVal(fp_rs,"Summary")%></td>
<td bgcolor='<%=bgcolor%>'><%If FP_FieldVal(fp_rs,"Status")="0" Then Response.Write("New") Else If FP_FieldVal(fp_rs,"Status")="1" Then Response.Write("Assigned") Else If FP_FieldVal(fp_rs,"Status")="2" Then Response.Write("Planning") Else If FP_FieldVal(fp_rs,"Status")="3" Then Response.Write("Scheduled") Else If FP_FieldVal(fp_rs,"Status")="4" Then Response.Write("Work In Progress") Else If FP_FieldVal(fp_rs,"Status")="5" Then Response.Write("Pending") Else If FP_FieldVal(fp_rs,"Status")="6" Then Response.Write("Resolved") Else If FP_FieldVal(fp_rs,"Status")="7" Then Response.Write("Closed") End If%></td>
<td bgcolor='<%=bgcolor%>'><% If FP_FieldVal(fp_rs,"PlanStartDate")="1/1/1990 12:12:12 PM" Then Response.Write(" ") Else Response.Write FP_FieldVal(fp_rs,"PlanStartDate") %></td>
<td bgcolor='<%=bgcolor%>'><% If FP_FieldVal(fp_rs,"PlanEndDate")="1/1/1990 12:12:12 PM" Then Response.Write(" ") Else Response.Write FP_FieldVal(fp_rs,"PlanEndDate") %></td>
<td bgcolor='<%=bgcolor%>'><% If FP_FieldVal(fp_rs,"ActualStartDate")="1/1/1990 12:12:12 PM" Then Response.Write(" ") Else Response.Write FP_FieldVal(fp_rs,"ActualStartDate") %></td>
<td bgcolor='<%=bgcolor%>'><% If FP_FieldVal(fp_rs,"ActualEndDate")="1/1/1990 12:12:12 PM" Then Response.Write(" ") Else Response.Write FP_FieldVal(fp_rs,"ActualEndDate") %></td>
<td bgcolor='<%=bgcolor%>' nowrap><b>C: </b><%=FP_FieldVal(fp_rs,"Category")%><br><b>T: </b><%=FP_FieldVal(fp_rs,"Type")%><br><b>I: </b><%=FP_FieldVal(fp_rs,"Item")%></td>
<td bgcolor='<%=bgcolor%>'><%=FP_FieldVal(fp_rs,"Maker")%> </td>
<td bgcolor='<%=bgcolor%>'><%=FP_FieldVal(fp_rs,"Checker")%> </td>
<td bgcolor='<%=bgcolor%>'><%=FP_FieldVal(fp_rs,"Verifier")%> </td>
<td bgcolor='<%=bgcolor%>' align="center" nowrap><a href="http://csg99rdpc001/RemedyLink/<%=RTrim(FP_FieldVal(fp_rs,"TicketNo"))%>Wklg.rtf" target="_blank"><% If RTrim(FP_FieldVal(fp_rs,"WorkLogLink"))="True" Then Response.Write("View Log") Else Response.Write(" ") %></a></td>
<td bgcolor='<%=bgcolor%>' align="center" nowrap><a href="http://csg99rdpc001/RemedyLink/<%=RTrim(FP_FieldVal(fp_rs,"TicketNo"))%>Adif.rtf" target="_blank"><% If RTrim(FP_FieldVal(fp_rs,"AddlInfoLink"))="True" Then Response.Write("View<br>Add'l Info") Else Response.Write(" ") %></a></td>
</tr>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
</body>
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: View records newer than 7 days - 2/7/2007 16:28:42
quote:
AND (iLastModified >= '#' & DateAdd(d,-7,Date()) & '#)"
See my edit above as it should be:
...AND (iLastModified >= #" & DateAdd(d,-7,Date()) & "#)"
that any better?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jgeatty
Posts: 199
Joined: 10/14/2004
Status: offline
|
RE: View records newer than 7 days - 2/7/2007 16:31:11
If I put in the last that you just took the quote out of I get this error:
Microsoft VBScript runtime error '800a0005'
Invalid procedure call or argument: 'DateAdd'
/remedy/test.asp, line 52
| |
|
|
|
BeTheBall
Posts: 6385
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: View records newer than 7 days - 2/7/2007 19:11:56
Roger would know better than I as I haven't used SQL Server much, but I think it uses single quotes instead of the #. Also, I think it uses getDate instead of date. Something like this:
AND (iLastModified >= '" & DateAdd(d,-7,getDate()) & "')"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
jgeatty
Posts: 199
Joined: 10/14/2004
Status: offline
|
RE: View records newer than 7 days - 2/8/2007 17:31:40
Roger,
With the above code, I get:
Database Results Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
One or more form fields were empty. You should provide default values for all form fields that are used in the query.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: View records newer than 7 days - 2/9/2007 9:23:09
Can you post the full SQL you're using?
As I said, I used that exact 'blurb' on one of my SQL Server 2K machines and it worked fine so there must be some little 'tweak' we need to do.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jgeatty
Posts: 199
Joined: 10/14/2004
Status: offline
|
RE: View records newer than 7 days - 2/9/2007 12:53:06
Here's my DRW code:
<%
fp_sQry="SELECT * FROM MainDataRespository WHERE OwnerFullName LIKE '%::OwnerFullName::%' AND Status<'6' AND (iLastModified >=('" & DateAdd("d",-7,Date()) & "') ORDER BY TicketNo DESC"
fp_sDefault="OwnerFullName="
fp_sNoRecords="<tr><td colspan=11 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="remedy"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=100
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=18
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
| |
|
|
|
BeTheBall
Posts: 6385
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: View records newer than 7 days - 2/9/2007 12:57:15
Is status a text field or numeric? If numeric, delete the single quotes around the number 6. If text, I would change:
Status<'6'
to:
CInt(Status) < 6
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
jgeatty
Posts: 199
Joined: 10/14/2004
Status: offline
|
RE: View records newer than 7 days - 2/9/2007 12:59:50
It's numeric so I dropped the single quote but still have the same error with the: ...AND (iLastModified >=('" & DateAdd("d",-7,Date()) & "')"
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: View records newer than 7 days - 2/9/2007 13:16:33
quote:
fp_sDefault="OwnerFullName="
Just for a quick check, what happens when you put this in that pace:
fp_sDefault="OwnerFullName=%"
That make any difference? If not, try putting this right after the code you posted:
<%=fp_sQry%>
Just checking to see *exactly* what query we're sending to the DB.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
jgeatty
Posts: 199
Joined: 10/14/2004
Status: offline
|
RE: View records newer than 7 days - 2/9/2007 13:39:50
It seems to be working correctly... this is the results:
SELECT * FROM MainDataRespository WHERE OwnerFullName LIKE '%::OwnerFullName::%' AND Status<6 AND (iLastModified >=('2/2/2007') ORDER BY TicketNo DESC
| |
|
|
|
jgeatty
Posts: 199
Joined: 10/14/2004
Status: offline
|
RE: View records newer than 7 days - 2/9/2007 13:56:07
Cool... that worked! Thanks for the help!!!
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
