SQL Query problem- Displaying data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query problem- Displaying data

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2003-12-04 : 16:05:21

My Table looks like below:

JobDate |Name |CheckOne| CheckTwo|CheckThree|CheckFour
________________________________________________________
12/4/2003|John |Elec | | |
12/4/2003|Tim | |Wiring |Cond |
12/4/2003|Kay | | | |AirCheck
12/4/2003|John | |Wiring |Cond |

I got the table populated when a person submits the daily jobs done by him. I have CheckOne, CheckTwo etc fields populated if the corresponding check box on the form is checked.

Now if somebody wants to track these daily activities, they can do so by entering the desired date.

For this purpose, I have made a date field on the form in another htm page and when the date entered is 12/4/2003, i would like to display something as below for the above data:

Name |Jobs Attended
_______________________
John |Elec,Wiring,Cond
Tim |Wiring,Cond
Kay |AirCheck

I wrote the query as below but it is not displaying in the desired format.

strSQL= "SELECT * FROM Mytable WHERE JobDate='"&ddate&"' "
strSQL= strSQL & "Group By CheckOne,CheckTwo,CheckThree,CheckFour,Name "

I am displaying as follows:

<table border="1">
<tr>
<th bgcolor="#0000FF"> <font color="#FFFFFF"> Name</font> </th>
<th bgcolor="#0000FF"> <font color="#FFFFFF"> Duties Attended</font> </th>
</tr>

<%do until rsObj.eof%>

<tr>

<td bgcolor="#FFFFFF"> <%=rsObj("Name")%> </td>

<td bgcolor="#FFFFFF"> <b><%=rsObj("CheckOne")%>,<%=rsObj("CheckTwo")%>,<%=rsObj("CheckThree")%>,<%=rsObj("CheckFour")%></b> </td>
</tr>
<%rsObj.movenext
loop
rsObj.Close
%>

</table>

How can i check and eliminate if the field is empty while displaying.

Any Suggestions.

Thanks in advance.

VJ

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-04 : 16:21:55

If you are trying to preserve the border in the table for null values, One option is to add a non-breaking space < > after each value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources