Help in Select Query

AmitavaCh
AmitavaCh used Ask the Experts™
on
I have one table for our project team, which Project team has to update on a daily basis.
The table has 4 columns and some 20-25 rows.
-----------
COLUMN
-----------
Project Name          Emp_Code           Status                            Remarks

The column 'STATUS' has three variables like Completed, WIP, Pending.
Team has to update in 'REMARKS' column where status is either 'In Progress' or 'Pending' individually.  The form is working fine.
However, I would like to hide all rows, where status is 'COMPLETED'. Can you help me please.
e.g. If RecSta1, RecSta2, RecSta3 or RecSta4 is 'Completed' those row should not be displayed.
Can you help me please.
I have attached the code & db file for your reference.
"SELECT RecID, RecCode, RecSta1, RecRem1, RecSta2, RecRem2, RecSta3, RecRem3, RecSta4, RecRem4 FROM Project where RecCode='10409' ORDER BY RecID ASC"

*****MY CODE******
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
If Request("Submit") <> "" Then
	intRecIDs = Replace(Request("hidRecIDs"), "*", "")  ' remove all the asterisks, to create a list like this: 2, 5, 8, 9 etc.
	arrRecIDs = Split(intRecIDs, ", ")				' Create an array, wich will contain just the IDs of the records we need to update
	For i = 0 to Ubound(arrRecIDs)					' Loop trough the array

		strTexa = Replace(Request("txtRem1" & arrRecIDs(i)), "'", "''")
		strTexb = Replace(Request("txtRem2" & arrRecIDs(i)), "'", "''")
		strTexc = Replace(Request("txtRem3" & arrRecIDs(i)), "'", "''")
		strTexd = Replace(Request("txtRem4" & arrRecIDs(i)), "'", "''")
		
		set commUpdate = Server.CreateObject("ADODB.Command")
		commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("Project.mdb") & ";Persist Security Info=False"
		commUpdate.CommandText = "UPDATE ProjectT  SET RecRem1 = '" & strTexa & "', RecRem2 = '" & strTexb & "', RecRem3 = '" & strTexc & "', RecRem4 = '" & strTexd & "' WHERE RecID = " & arrRecIDs(i)
		commUpdate.CommandType = 1
		commUpdate.CommandTimeout = 0
		commUpdate.Prepared = true
		commUpdate.Execute()
	Next
	strMessage = i & " Records Updated"
	Response.Redirect("Project.asp?Message=" & strMessage)
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("Project.mdb") & ";Persist Security Info=False"
Recordset1.Source = "SELECT RecID, RecSta1, RecRem1, RecSta2, RecRem2, RecSta3, RecRem3, RecSta4, RecRem4 FROM ProjectT where RecCode='10409' ORDER BY RecID ASC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Attendance Management</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">
<!--
//  When the value in a textfield is changed, notice the onChange="RecUpdate('<%= intRecID %>')"
//  on each of the textfields,  the value of the Record ID associated with that field
//  is passed to the RecUpdate function. First the value is surounded with 2 asterisks e.g. *6*
//  This is so that *1* can be distinguished from *10*, *11* etc.
function RecUpdate(RecID){
var ThisID = "*" + (RecID) + "*"
if (document.form1.hidRecIDs.value == ""){	// If the hidden field is empty
document.form1.hidRecIDs.value = (ThisID)	// Store the value in the hidden field (hidRecIDs) as it is.
}
if (document.form1.hidRecIDs.value != ""){  // If the hidden field isn't empty
var str = document.form1.hidRecIDs.value;	// Store the contents of the hidden field in the variable str
var pos = str.indexOf(ThisID);				// Search str to see if this RecID is allready in it.
if (pos == -1) {							// If the position returned is -1 it isn't allredy in there,  
document.form1.hidRecIDs.value = document.form1.hidRecIDs.value + ", " + (ThisID)  
} 											// so add ", " and this ID to what is already in hidRecIDs 
}											// to create a list like this *2*, *5*, *8* etc.	
}
//-->
</script>
</head>
<body>
<font size="2" face="Arial, Helvetica, sans-serif"><%= Request.QueryString("Message") %></font>
<form name="form1" method="post" action="Project.asp">
<table width="613" border="0" cellpadding="0" cellspacing="0">
      <% 
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) 
%>
<% intRecID =(Recordset1.Fields.Item("RecID").Value) ' Store the current RecordID in a variable %>  <tr>
    <td colspan="3" bgcolor="#FFCC99">&nbsp;<font size="2" face="Arial"> Emp ID.:<%= intRecID %><input name="hidRecID<%= intRecID %>" type="hidden" value="<%= intRecID %>" size="5" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
	</td>
  </tr>
  <tr>
    <td align="center" width="227" bgcolor="#CCFFCC">
	<font size="1" face="Arial">Date</font></td>
    <td align="center" width="74" bgcolor="#CCFFCC">
	<font size="1" face="Arial">Status</font></td>
    <td align="center" width="311" bgcolor="#CCFFCC">
	<font size="1" face="Arial">Remarks</font></td>
  </tr>
  <tr>
    <td align="center" width="227" bgcolor="#CCFFCC">&nbsp;</td>
    <td align="center" width="74" bgcolor="#CCFFCC">&nbsp;</td>
    <td align="center" width="311" bgcolor="#CCFFCC">&nbsp;</td>
  </tr>
  <tr>
    <td width="227" bgcolor="#CCFFCC">
	<font size="1" face="Arial">&nbsp; Project - 1</font></td>
    <td width="74" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
		<input name="txtSta1<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecSta1").Value)%>" size="10" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC; text-align:center"></font></td>
    <td width="311" align="center" bgcolor="#CCCC00"><font size="1" face="Arial, Helvetica, sans-serif">
		<input name="txtRem1<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecRem1").Value)%>" size="45" style="font-family: Arial; font-size: 8pt; background-color:#FFFF00"></font></td>
  </tr>
  <tr>
    <td width="227" bgcolor="#CCFFCC">
	<font size="1" face="Arial">&nbsp; Project - 2</font></td>
    <td width="74" align="center" bgcolor="#CCCCCC">
	<font size="1" face="Arial, Helvetica, sans-serif">
		<input name="txtSta2<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecSta2").Value)%>" size="10" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC; text-align:center"></font></td>
    <td width="311" align="center" bgcolor="#CCCC00"><font size="1" face="Arial, Helvetica, sans-serif">
		<input name="txtRem2<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecRem2").Value)%>" size="45" style="font-family: Arial; font-size: 8pt; background-color:#FFFF00"></font></td>
  </tr>
  <tr>
    <td width="227" bgcolor="#CCFFCC">
	<font size="1" face="Arial">&nbsp; Project - 3</font></td>
    <td width="74" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
		<input name="txtSta3<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecSta3").Value)%>" size="10" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC; text-align:center"></font></td>
    <td width="311" align="center" bgcolor="#CCCC00"><font size="1" face="Arial, Helvetica, sans-serif">
		<input name="txtRem3<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecRem3").Value)%>" size="45" style="font-family: Arial; font-size: 8pt; background-color:#FFFF00"></font></td>
  </tr>
  <tr>
    <td width="227" bgcolor="#CCFFCC">
	<font size="1" face="Arial">&nbsp; Project - 4</font></td>
    <td width="74" align="center" bgcolor="#CCCCCC"><font size="1" face="Arial, Helvetica, sans-serif">
		<input name="txtSta4<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecSta4").Value)%>" size="10" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC; text-align:center"></font></td>
    <td width="311" align="center" bgcolor="#CCCC00"><font size="1" face="Arial, Helvetica, sans-serif">
		<input name="txtRem4<%= intRecID %>" type="text" onChange="RecUpdate('<%= intRecID %>')" value="<%=(Recordset1.Fields.Item("RecRem4").Value)%>" size="45" style="font-family: Arial; font-size: 8pt; background-color:#FFFF00"></font></td>
  </tr>
  <tr>
    <td width="613" colspan="3"><b>&nbsp; </b></td>
  </tr>
      <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>
</table>
<br>
<input name="hidRecIDs" type="text" size="40">
<font size="2" face="Arial, Helvetica, sans-serif">Selected Records</font><br>
<br>
<font size="2" face="Arial, Helvetica, sans-serif">
<input type="submit" name="Submit" value="Update">
</font>
<input name="hidRecID<%= intRecID %>" type="hidden" value="<%= intRecID %>" size="5" style="font-family: Arial; font-size: 8pt; background-color:#CCCCCC">
</form>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Open in new window

Project.mdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
add

RecSta1<>'COMPLETED' and RecSta2<>'COMPLETED' and RecSta3<>'COMPLETED' and RecSta4<>'COMPLETED'

to your select query



Recordset1.Source = "SELECT RecID, RecSta1, RecRem1, RecSta2, RecRem2, RecSta3, RecRem3, RecSta4, RecRem4 FROM ProjectT where RecCode='10409' and RecSta1<>'COMPLETED' and RecSta2<>'COMPLETED' and RecSta3<>'COMPLETED' and RecSta4<>'COMPLETED' ORDER BY RecID ASC"

Open in new window

Commented:
Modify your SELECT statement like this:
Recordset1.Source = "SELECT RecID, RecSta1, RecRem1, RecSta2, RecRem2, RecSta3, RecRem3, RecSta4, RecRem4 FROM ProjectT where RecCode='10409'
and (RecSta1<>'COMPLETED' or RecSta2<>'COMPLETED' or RecSta3<>'COMPLETED' or RecSta4<>'COMPLETED') ORDER BY RecID ASC"

Author

Commented:
Thank you. I have redesigned my form a little bit to adjust.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial