Dev tool: Dreamweaver MX
DB: Access 2000
Technology: ASP 2.0
OS: Win 2000
Language: VB Script
My Skill Level: Beginner

I'm writing a Timesheet Application that integrates QuickBooks 2002 with Access 2000. The Employees log-in online, fill out their timesheets, and that information is stored in Access. Once a week, the administrator runs a program that copies the timesheet info to QB, which is running on his desktop. At that time, it resets a flag in each record of the timesheets table that tells us it has been Processed (The Processed field stores a simple Yes/No).

So far, all this works without a hitch.

I'm having trouble with something simple. I need to create two different pages: one that returns all the new timesheet entries (Processed = "False") and another page that returns all the old timesheet entries for the past 31 days (Processed = "True" and DateTaught = "CurrentDate -31" or something like that).

Can someone take a look at this code (generated by Dreamweaver MX) and help me tweak it to return the ranges I need?

I greatly appreciate all help!

--------------------------------------------------

<!--#include file="../Connections/myConnection.asp" -->
<%
Dim RecsetTimesheets__MMColParam
RecsetTimesheets__MMColParam = "1"
If (Request.QueryString("CustomerTutorID") <> "") Then
RecsetTimesheets__MMColParam = Request.QueryString("CustomerTutorID")
End If
%>
<%
Dim RecsetTimesheets
Dim RecsetTimesheets_numRows

Set RecsetTimesheets = Server.CreateObject("ADODB.Recordset")
RecsetTimesheets.ActiveConnection = MM_myConnection_STRING
RecsetTimesheets.Source = "SELECT * FROM Timesheets WHERE Tutor_CustomerTutorID = " + Replace(RecsetTimesheets__MMColParam, "'", "''") + " ORDER BY DateTaught ASC"
RecsetTimesheets.CursorType = 0
RecsetTimesheets.CursorLocation = 2
RecsetTimesheets.LockType = 1
RecsetTimesheets.Open()

RecsetTimesheets_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
RecsetTimesheets_numRows = RecsetTimesheets_numRows + Repeat1__numRows
%>
<link href="../common/mainstyle.css" rel="stylesheet" type="text/css">

<table width="500" border="0" cellspacing="0" cellpadding="3">
<tr>
<td class="formLabels"><p><span class="bigText">Current Timesheet</span></p>
<p> These items have not yet been processed, but will appear on your next
paycheck.</p></td>
</tr>
<tr>
<td class="formLabels">&nbsp;</td>
</tr>
</table>

<table width="500" border="0" cellspacing="0" cellpadding="3">
<tr class="formHeaderCell">
<td width="20%">Date</td>
<td width="40%">Student</td>
<td width="20%">Hours</td>
<td width="20%">&nbsp;</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT RecsetTimesheets.EOF))
%>
<tr>
<td class="formLabels"><%=(RecsetTimesheets.Fields.Ite m("DateTaught").Value)%></td>
<td class="formLabels"><%=(RecsetTimesheets.Fields.Ite m("StudentName").Value)%></td>
<td class="formLabels"><%=(RecsetTimesheets.Fields.Ite m("Hours").Value)%></td>
<td class="formLabels"><%=(RecsetTimesheets.Fields.Ite m("Processed").Value)%></td>
</tr>
<tr>
<td colspan="4" class="formLabels"><%=(RecsetTimesheets.Fields.Ite m("Notes").Value)%></td>
</tr>
<tr>
<td colspan="4"> <div align="center"><img src="../images/darkBorderline.gif" width="490" height="1"></div></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
RecsetTimesheets.MoveNext()
Wend
%>

</table>


<%
RecsetTimesheets.Close()
Set RecsetTimesheets = Nothing
%>

I've worked on this for two days, and then within minutes of posting a plea for help, I find the answer.

I modivied my SQL statement to this:

RecsetTimesheets.Source = "SELECT * FROM Timesheets WHERE Tutor_CustomerTutorID = " + Replace(RecsetTimesheets__MMColParam, "'", "''") + " AND Processed <> -1 ORDER BY DateTaught ASC"

I added this: AND Processed <> -1

I think my problem was that I was giving it the wrong parameters to test for. I was trying "Yes," "True" and "1." I had no idea it was "-1."

Very odd.

Next up, figuring out how to limit the date range (I've changed my mind and will probably just use drop downs to select the available date ranges.