Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Vancouver
    Posts
    17

    Unanswered: help with doing a Search using Dates

    Hi folks..i am new to this forum and fairly new to asp so hope you bear (bare?)with me

    I have an Access2003 DB where i have a query called Attendance Query. I want to run a Search where the users can enter a StartDate and an EndDate and then the results will output in a table for all records within that time frame. i have tried to adapt my other simple text searches for this, but i am not having much luck. My query has the following fields: newDate (date), FullName(text), Reason(text), Justified(Yes/No)
    When i run the code i get Expected end of statementon the sql line, though i am certain that not the only problem here. Here is my code: i have stripped out some of the html stuff to make it more readable.
    Code:
    <%@ Language=VBScript %>
    <%	
    dim conn
     Session.timeout = 2
    Set conn = Server.CreateObject("ADODB.Connection")
        conn.open "attendance2004","",""
    %>
    <%
    select case request.querystring("Action")
    case "Search"
        	sql="SELECT * FROM attendancequery WHERE newdate BETWEEN #"StartDate"# AND #"EndDate"# " 
        Set rs = Server.CreateObject("ADODB.Recordset")
        rs.Open sql, conn, 3, 3
    end select
    %>
    <%
    Select case request.querystring("Action")
     case "Search"
    %>
    <table WIDTH="100%" BORDER="0" BGCOLOR="#ffffff" CELLSPACING="0"> 
    
    <TBODY>
    <% Do While Not RS.EOF %>
    <% if ucase(rs("justified"))="ON" then
    			sJustified="Yes"
    		else
    			sJustified="No"
    		end if %>
      <tr VALIGN="TOP">
        <td><%Response.Write RS("newdate")%></td>
        <td><%Response.Write RS("fullname")%></td>
       <td><%Response.Write RS("reason")%></td>
        <td><%Response.Write sJustified%></td>
      </tr>
      </font><font COLOR="#000000" FACE="Arial" size="2">
      <% RS.MoveNext %>
      <% loop %>  
      </table>
    <% case else%>
    
    <form ACTION="reportattendanceByDateA.asp" METHOD="GET">
      <font face="Arial">Start Date</font><input NAME="startdate" size="13"><br>
    End Date <input type="text" name="enddate" size="14">
     <input TYPE="Submit" VALUE="Search" name="Action"></p>
    </form>
    <p>
    <%End Select%>
    Anyone got ideas? Also, in my Response.write(s), does the case of the fields have to match ExaCTly the way the field is in my Access DB
    many thanks
    still in the baby steps of coding

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    replace this
    Code:
    sql="SELECT * FROM attendancequery WHERE newdate BETWEEN #"StartDate"# AND #"EndDate"# "
    with this...

    Code:
    StartDate = Request("startdate")
    EndDate= Request("enddate")
    sql="SELECT * FROM attendancequery WHERE newdate BETWEEN #" & StartDate & "# AND #" & EndDate & "# "
    that should get things working a bit better....

  3. #3
    Join Date
    Feb 2004
    Location
    Vancouver
    Posts
    17

    help INSERTING data to a table

    thanks that got my search working, but it has brought to light another issue with my DB.

    In a form i have the following code:
    Code:
    <%	
    	Do while not rs.EOF%>        
    		<option value="<%Response.Write rs.fields("employeeno").value%>"><%Response.Write rs.fields("fullname").value%></option>
    <% rs.MoveNext%><%loop%> </select>
    where i am showing the user 1 thing (name), but picking up something else (employeeno)

    In my asp code, when i send this info from the form to the database i have:
    Code:
    	sValue1 = Request.Form("employee")
    	sValue1 = Request.Form("reason")
    
    ...and    	<%
    		sql1 = "Insert Into attendancetbl (employeeno, reason, .....) values ('" & sValue1 & "','" & sValue2 & "',...
    How can i adjust this code so that is also pics up the 'FullName' that i am displaying in the form and puts that value into FullName field of my table.

    hmmm was that confusing? essentially i want both what i am displaying AND what i am picking up from the form (fullname and employeeno) to be deposited into my table and not just the 'employeeno'
    thanks
    still in the baby steps of coding

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    the simpliest way to do what you want is to have your value field contain a concatenation of the data so inside of
    Code:
    <option value="<%Response.Write rs.fields("employeeno").value%>"
    have something like
    Code:
    <option value="<%Response.Write rs.fields("employeeno").value&"|"& rs.fields("fullname").value%>"
    then when you request the value you will be able to use the split function to get the number and the name.

    HTH

  5. #5
    Join Date
    Feb 2004
    Location
    Vancouver
    Posts
    17

    help INSERTING data to a table

    Ok i tried that, but i still cant get the FullName into the table. i adjusted my form to:
    Code:
    <option value="<%Response.Write rs.fields("employeeno").value&"|"& rs.fields("fullname").value%>"><%Response.Write rs.fields("fullname").value%></option>
    but i think i have the part in my other asp wrong, where i send the data to the db

    I adjusted that code liek this:
    Code:
    	sValue1 = Request.Form("employee")
    	sValue2 = Request.Form("reason")
    	sValue3 = Request.Form("fullname")
    ......
    	<%
    		sql1 = "Insert Into attendancetbl (employeeno, FullName, reason...) values ('" & sValue1 & "','" & sValue3 & "','" & sValue2 & "' ...);"
    Yet when i try this out my FullName field in my table is still blank
    Am i doing this part wrong? Is there a special way of using a split function?
    thanks
    still in the baby steps of coding

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie, assuming that employee is your select box.
    Code:
    arrEmployee = split(request("employee"), "|")
    employeeno = arrEmployee(0)
    employeename = arrEmployee(1)
    
    sql1 = "Insert Into attendancetbl (employeeno, FullName...) values ('" & employeeno & "','" & employeename & "'...);"
    you might want to check the syntax for the split function.... I think that is right though.

  7. #7
    Join Date
    Feb 2004
    Location
    Vancouver
    Posts
    17
    thats great It worked
    thank you
    still in the baby steps of coding

Posting Permissions

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