help with doing a Search using Dates

jaberwocky · #1 (**permalink**) 02-17-04, 18:14

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

rokslide · #2 (**permalink**) 02-17-04, 18:30

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

jaberwocky · #3 (**permalink**) 02-17-04, 19:02

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

rokslide · #4 (**permalink**) 02-17-04, 19:18

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

jaberwocky · #5 (**permalink**) 02-17-04, 19:37

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

rokslide · #6 (**permalink**) 02-17-04, 19:43

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.

jaberwocky · #7 (**permalink**) 02-18-04, 13:27

thats great It worked
thank you

Internet Services

Web Development

Digital Marketing

Consumer Tech