Need to filter the records by date on a select box

[axslearnr]

Hi
I have a combo box where I select the item in it to populate a select box with the records of that type. When I click on the record in the select box it displays me the employee details like emp name, date trained of that particular item. I need t o add filter so where I can display only empnames who r trained between certain date.
I already wrote the code to pull up the empnames and date trained but i need to add the filter?

How to do that? Pls let me know
thank you. here is the part of the code



<form method="POST" name="TCForm" action="RptTrainingbyTypeCode.asp?menuid=<%=menuid%>&typeid=<%=TType%>">
<table cellspacing="0" cellpadding="0" width="900">
<td valign=" width="50%" top" width="138">
<font face="Arial, Helvetica, sans-serif"> <b>Training Type:</b> &nbsp; </font>&nbsp;
&nbsp;&nbsp;

<td valign="top" width="762">
<SELECT name="TrainType" size="1" onchange="SubmitMe()">
<%
'Populate the drop down menu with training code names


response.write "<OPTION VALUE = -1> </OPTION>"
Do While NOT RST.EOF
if cint(RST("Training_Method_ID")) = TType then
Response.Write "<OPTION VALUE='" & RST("Training_Method_ID") & "' SELECTED>"
Response.Write RST("Training_Method_Text") & "</OPTION>"

else
Response.Write "<OPTION VALUE='" & RST("Training_Method_ID") & "'>"
Response.Write RST("Training_Method_Text") & "</OPTION>"

end if
RST.MoveNext
Loop

rst.close
set rst = nothing



%>

</SELECT>&nbsp;

<form method="POST" action="RptTrainingbyTypeCode.asp?menuid=<%=menuid%>&typeid=Request.querystring("typeid")&classid=Request.querystring("Class_ID")" name="frmAudit">
<font FACE="Trebuchet MS, Arial, Helvetica" size="2">

<b>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Start Date:</b></font>&nbsp;<!--webbot bot="Validation" S-Data-Type="String" B-Allow-Digits="TRUE" I-Maximum-Length="2" --><input type="text" name="BeginMonth" size="2" value=<%=Month(BDate)%> tabindex="1" maxlength="2" onchange="return CheckMonth(BeginMonth.value)">
/ &nbsp;<input type="text" name="BeginDay" size="2" value=<%=Day(BDate)%> tabindex="2" maxlength="2" onchange="return CheckDay(BeginDay.value)">
/ <input type="text" name="BeginYear" size="4" value=<%=Year(BDate)%> tabindex="3" maxlength="4" onchange="return CheckYear(BeginYear.value)">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<font FACE="Trebuchet MS, Arial, Helvetica" size="2"><b>End Date:&nbsp;</b></font><input type="text" name="EndDateMonth" size="2" value=<%=Month(Date)%> tabindex="4" maxlength="2" onchange="return CheckMonth(EndDateMonth.value)">
/ <input type="text" name="EndDateDay" size="2" value=<%=Day(Date)%> tabindex="5" maxlength="2" onchange="return CheckDay(EndDateDay.value)">
/ <input type="text" name="EndDateYear" size="4" value=<%=Year(Date)%> tabindex="6" maxlength="4" onchange="return CheckYear(EndDateYear.value)">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input type="Submit" value=" Filter " name="BtnFilter" onClick()="RptTrainingbyTypeCode.asp?menuid=<%=menuid%>&typeid=Request.querystring("typeid")&classid=Request.querystring("Class_ID")>

</Table>

<Table>

<%

StartDate = request("BeginMonth") & "/" & request("BeginDay") & "/" & request("BeginYear")

Response.write request("BeginMonth")
'if len(StartDate) > 2 then
'Get date values entered in fields
'StartDate = DateValue(StartDate)
'EndDate = DateValue(request("EndDateMonth") & "/" & request("EndDateDay") & "/" & request("EndDateYear"))
EndDate= request("EndDateMonth") & "/" & request("EndDateDay") & "/" & request("EndDateYear")
'Set date values that are displayed on form
EDate = EndDate
BDate = StartDate
'end if


Response.write request.Form("BeginMonth")
Response.write request("EndDateMonth")
Response.write "gdgd"



strSQL = "SELECT employee.[Employee ID], employee.[First Name], employee.[Middle Name], employee.[Last Name], employee_training.Training_Method_ID, "
strSQL = strSQL & " employee_training.Train_ID, employee_training.Training_Date FROM "
strSQL = strSQL & "employee INNER JOIN employee_training ON employee.[Employee ID] = employee_training.Employee_ID "
strSQL = strSQL & "WHERE (((employee_training.Training_Method_ID)=" & TType & ") AND ((employee_training.Train_ID)=" & Class_ID & ")) ORDER BY employee.[Last Name], employee.[First Name], employee_training.training_date DESC;"


Set RST=Server.CreateObject("adodb.recordset")
rst.open strSQL, conn

if not rst.eof then
rst.MoveFirst

response.write "<tr><td><b><u>Employee Name</u></b></td><td align='center'><b><u>Date Trained</u></b></td><td><b><u>Employee Name</u></b></td><td align='center'><b><u>Date Trained</u></b></td></tr>"

CurrentName = -1
NameCount = 1
'Go through list and write information. Eliminate duplicates.
do while not rst.eof
if CurrentName <> rst("Employee ID") then 'Different Names. Print
if NameCount = 1 then
response.write "<tr>"
end if

Response.Write " <td width='200'>" & rst("First Name") & " " & rst("Middle Name") & " " & rst("Last Name") & "</td><td width='200' align='center'>" & rst("Training_Date") & "</td> "

if NameCount = 2 then
response.write "</tr>"
NameCount = 1
Else
NameCount = NameCount + 1
end if

CurrentName = rst("Employee ID")
' else Names are same. Do not print duplicates.
end if

rst.movenext 'Go to next record
loop



end if
rst.close
set rst = nothing
conn.close
set conn = nothing

end if
%>

 

[DreXor]

Change this :

strSQL = "SELECT employee.[Employee ID], employee.[First Name], employee.[Middle Name], employee.[Last Name], employee_training.Training_Method_ID, "
    strSQL = strSQL & " employee_training.Train_ID, employee_training.Training_Date FROM "
    strSQL = strSQL & "employee INNER JOIN employee_training ON employee.[Employee ID] = employee_training.Employee_ID "
    strSQL = strSQL & "WHERE (((employee_training.Training_Method_ID)=" & TType & ") AND ((employee_training.Train_ID)=" & Class_ID & "))  ORDER BY employee.[Last Name], employee.[First Name], employee_training.training_date DESC;"

to something along the lines of :

strSQL = "SELECT employee.[Employee ID], employee.[First Name], employee.[Middle Name], employee.[Last Name], employee_training.Training_Method_ID, "
    strSQL = strSQL & " employee_training.Train_ID, employee_training.Training_Date FROM "
    strSQL = strSQL & "employee INNER JOIN employee_training ON employee.[Employee ID] = employee_training.Employee_ID "
    strSQL = strSQL & "WHERE (((employee_training.Training_Method_ID)=" & TType & ") AND ((employee_training.Train_ID)=" & Class_ID & "))[b] AND Training_Date Between #" &  request("BeginDateMonth") & "# and #" &  request("EndDateMonth") & "# [/b]ORDER BY employee.[Last Name], employee.[First Name], employee_training.training_date DESC;"

just need to tack on to the conditions of the query, may also want to qualify that there is values in the requested values, and on top of that, they are dates, and the second is "greater" than the first.

DreX
aKa - Robert

 

[axslearnr]

Hi
Thanks for thr reply. I cant get the date values from the form. I get null values for these fields.

Request("BeginMonth")
Request("EndMonth")


Any wrong in this code

Start Date:<input type="text" name="BeginMonth" size="2" value=<%=Month(BDate)%> tabindex="1" maxlength="2" onchange="return CheckMonth(BeginMonth.value)">
/ &nbsp;<input type="text" name="BeginDay" size="2" value=<%=Day(BDate)%> tabindex="2" maxlength="2" onchange="return CheckDay(BeginDay.value)">
/ <input type="text" name="BeginYear" size="4" value=<%=Year(BDate)%> tabindex="3" maxlength="4" onchange="return CheckYear(BeginYear.value)">
<font FACE="Trebuchet MS, Arial, Helvetica" size="2"><b>End Date:&nbsp;</b></font><input type="text" name="EndDateMonth" size="2" value=<%=Month(Date)%> tabindex="4" maxlength="2" onchange="return CheckMonth(EndDateMonth.value)">
/ <input type="text" name="EndDateDay" size="2" value=<%=Day(Date)%> tabindex="5" maxlength="2" onchange="return CheckDay(EndDateDay.value)">
/ <input type="text" name="EndDateYear" size="4" value=<%=Year(Date)%> tabindex="6" maxlength="4" onchange="return CheckYear(EndDateYear.value)">

When I retrieve later in the form I get null values?

StartDate = request("BeginMonth") & "/" & request("BeginDay") & "/" & request("BeginYear")

Response.write request("BeginMonth")
'if len(StartDate) > 2 then
'Get date values entered in fields
'StartDate = DateValue(StartDate)
'EndDate = DateValue(request("EndDateMonth") & "/" & request("EndDateDay") & "/" & request("EndDateYear"))
EndDate= request("EndDateMonth") & "/" & request("EndDateDay") & "/" & request("EndDateYear")
'Set date values that are displayed on form
EDate = EndDate
BDate = StartDate
'end if


Can you pls telll me why?
thanks

 

[Tarwn]

If your trying to access those values later in the same form your shouldn't be getting values. The Response values aren't goingto be available until that form has been submitted by the client, andten they will be avaiulable to the page theform is submitted to. You will need to place your code to build the dates in the page that has the sql statement and then modify the sql statement to use the variabls wiuth the full dates in them instead of just the values for the months from the form.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[axslearnr]

Hi
The Date fields are on the same form as other ones. When I select the item in the select box it displays all the records. I need to put a filter so I put a date fields on the same form and trying to filter the records by using those datesif the user wants to filter the records by dates. How to do that? Do I have a way to do it ?

Please let me know

thank you

 

[amorous]

I dont see the form variable named EndMonth...
Then how can you get Request("EndMonth")

should it be Request("EndDate")

-VJ

 

[axslearnr]

Hi
I am sorry that was a typing mistake in the posting. I cant retrieve those values from the fields. I get null values. Is there any way to get the those date field values in the same form?
So I can filter the records

pls let me know

 

[amorous]

Dude...

How can you get the values of the form using Request("formvariablename") on the same form...with out submitting the form...

Clarify your problem step wise and someone here can help you out...

-VJ

 

[axslearnr]

Hi
This is my program stepwise.

Note: Everytime I select an item from the combo box or select box I havea wrote function so it reruns the whole ReportTrainingType ASP page. with this function

1) I have a combo box on a form which has three values. TP,SP,IP. When I select one of the value from the combobox TP, or IP or SP

2) it gives me a list of records which I populate in the Select box I have.

3) When I click on the select box item it gives me the records of the employee in that particular item. It gives me all the records.

4) I want only a certain number of records using the date fileds.

5) So I kept a date field on the form and retrieve those values.

I used this function to refresh the ReportTrainingType ASP page
function SubmitMe()
{

document.TCForm.submit();
return(true);
}

' This is the initial combo box
<form method="POST" name="TCForm" action="RptTrainingbyTypeCode.asp?menuid=<%=menuid%>&typeid=<%=TType%>">
<SELECT name="TrainType" size="1" onchange="SubmitMe()">
<%
'Populate the drop down menu with training code names


response.write "<OPTION VALUE = -1> </OPTION>"
Do While NOT RST.EOF
if cint(RST("Training_Method_ID")) = TType then
Response.Write "<OPTION VALUE='" & RST("Training_Method_ID") & "' SELECTED>"
Response.Write RST("Training_Method_Text") & "</OPTION>"

else
Response.Write "<OPTION VALUE='" & RST("Training_Method_ID") & "'>"
Response.Write RST("Training_Method_Text") & "</OPTION>"

end if
RST.MoveNext
Loop

rst.close
set rst = nothing
%>

' This is the Select box where i select the item to display all the emp records.

response.write "<SELECT name='TrainCode' size='10' onclick='SubmitMe()'>"

'Go through list and write information
do while not rst.eof

WriteClass(TType)
rst.movenext
loop
response.write "</Select>"
end if
rst.close
set rst = nothing

' This where I display the all the emp records.

Set RST=Server.CreateObject("adodb.recordset")
rst.open strSQL, conn

if not rst.eof then
rst.MoveFirst



response.write "<tr><td><b><u>Employee Name</u></b></td><td align='center'><b><u>Date Trained</u></b></td><td><b><u>Employee Name</u></b></td><td align='center'><b><u>Date Trained</u></b></td></tr>"

CurrentName = -1
NameCount = 1
'Go through list and write information. Eliminate duplicates.
do while not rst.eof
if CurrentName <> rst("Employee ID") then 'Different Names. Print
if NameCount = 1 then
response.write "<tr>"
end if

Response.Write " <td width='200'>" & rst("First Name") & " " & rst("Middle Name") & " " & rst("Last Name") & "</td><td width='200' align='center'>" & rst("Training_Date") & "</td> "

if NameCount = 2 then
response.write "</tr>"
NameCount = 1
Else
NameCount = NameCount + 1
end if

CurrentName = rst("Employee ID")
' else Names are same. Do not print duplicates.
end if

rst.movenext 'Go to next record
loop



end if
rst.close
set rst = nothing
conn.close
set conn = nothing

end if
%>


So this is my requirement? Pls help me

Thanks

 

[axslearnr]

Hi
Thanks for ur help. I still cant figure out how to do it? Please anyone suggest me.

thank you