Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Unanswered: Need to use a Yes/No field in DB to filter recordset...

    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
    %>

  2. #2
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Found Answer...

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •