Alternate Color within 2 While Statements

[Corneliu]

I have a Weekly Calendar where it shows the work categories for each user. There is currently just 1 table that the data comes from. I was asked to make that calendar so that when a user is late coming to work, then display that day in RED (column), else just normal white color.
I tried this various ways, but everytime I try it, it messes up the columns and/or rows. Anyone can give me a hand with this, PLEASE?

This is the current code, which displays the columns in white:
<tr bgcolor=&quot;#FFFFFF&quot;>
<%
Dim workingDay, workingDate
arrWeekDays = Array(&quot;&quot;, &quot;Sunday&quot;, &quot;Monday&quot;, &quot;Tuesday&quot;, &quot;Wednesday&quot;, &quot;Thursday&quot;, &quot;Friday&quot;, &quot;Saturday&quot
Dim dtNow
dtNow = Now()
For workingDay = 1 to 7

workingDate = DateAdd(&quot;d&quot;,workingDay,WeekStart)
dtIterate = DateAdd(&quot;d&quot;, workingDay,WeekStart)
%>
<td bgcolor=&quot;#FFFFFF&quot; valign=&quot;top&quot; width=&quot;14%&quot;>
<% Response.Write &quot;<em>&quot; & &quot;<span class=TimeTrackerDate>&quot; %>
<a href=&quot;Add/TimeAdd.asp?ReqDate=<%= FormatDateTime (workingDate, 2) %>&quot;><img src=&quot;../images/cal-plus.gif&quot; width=&quot;10&quot; height=&quot;10&quot; border=&quot;0&quot; align=&quot;left&quot; alt=&quot;Add a New Entry in your Calendar&quot;></a>
<a href=&quot;Reports/ListDates.aspx?DateID=<%= FormatDateTime (workingDate, 2) %>&TechnicianID=<%= TechID %>&quot;><img src=&quot;../images/SmallDelete.gif&quot; width=&quot;12&quot; height=&quot;11&quot; border=&quot;0&quot; align=&quot;right&quot; alt=&quot;Delete an Entry from your Calendar&quot;></a>
<% Response.Write arrWeekDays(WeekDay(dtIterate))%>
<% Response.Write &quot;<BR>&quot; & (FormatDateTime (workingDate, 2)) & &quot;</em>&quot; & &quot;</span>&quot; & &quot;<BR>&quot; %>
<% Do Until RS.EOF %>
<% If Day(rs(&quot;ReqDate&quot) <> Day(workingDate) Then Exit Do %>
<span class=&quot;CalendarHours&quot;><% Response.Write (RS(&quot;Category_Name&quot)%><center><img src=&quot;../images/d_arrow.gif&quot; align=&quot;texttop&quot;></center><center><% Response.Write RS(&quot;HrsOff&quot / 60 & &quot; Hrs&quot;%></center></span>
<hr size=&quot;1&quot;>
<% RS.MoveNext
Loop
NEXT
%>
</tr>



This is what I tried and it messes up the columns. It does not display anything but just the first column (Monday) and that is it. Anyone tell me where i go wrong?
New statement code:
<tr>
<%
Dim workingDay, workingDate
arrWeekDays = Array(&quot;&quot;, &quot;Sunday&quot;, &quot;Monday&quot;, &quot;Tuesday&quot;, &quot;Wednesday&quot;, &quot;Thursday&quot;, &quot;Friday&quot;, &quot;Saturday&quot
Dim dtNow
dtNow = Now()
For workingDay = 1 to 7

workingDate = DateAdd(&quot;d&quot;,workingDay,WeekStart)
dtIterate = DateAdd(&quot;d&quot;, workingDay,WeekStart)
%>
<% While NOT RS2.EOF
If Day(RS2(&quot;ReqDate&quot) <> Day(workingDate) Then
Dim bgcolor
bgcolor =&quot;#FFFFFF&quot;
else
bgcolor =&quot;#FF0000&quot;
%>
<td bgcolor=&quot;<%= bgcolor %>&quot; valign=&quot;top&quot; width=&quot;14%&quot;>
<% Response.Write &quot;<em>&quot; & &quot;<span class=TimeTrackerDate>&quot; %>
<a href=&quot;Add/TimeAdd.asp?ReqDate=<%= FormatDateTime (workingDate, 2) %>&quot;><img src=&quot;../images/cal-plus.gif&quot; width=&quot;10&quot; height=&quot;10&quot; border=&quot;0&quot; align=&quot;left&quot; alt=&quot;Add a New Entry in your Calendar&quot;></a>
<a href=&quot;Reports/ListDates.aspx?DateID=<%= FormatDateTime (workingDate, 2) %>&TechnicianID=<%= TechID %>&quot;><img src=&quot;../images/SmallDelete.gif&quot; width=&quot;12&quot; height=&quot;11&quot; border=&quot;0&quot; align=&quot;right&quot; alt=&quot;Delete an Entry from your Calendar&quot;></a>
<% Response.Write arrWeekDays(WeekDay(dtIterate))%>
<% Response.Write &quot;<BR>&quot; & (FormatDateTime (workingDate, 2)) & &quot;</em>&quot; & &quot;</span>&quot; & &quot;<BR>&quot; %>
<% End If %>
<% Do Until RS.EOF
If Day(rs(&quot;ReqDate&quot) <> Day(workingDate) Then Exit Do %>
<span class=&quot;<%=bgcolor%>&quot;><% Response.Write (RS(&quot;Category_Name&quot)%><center><img src=&quot;../images/d_arrow.gif&quot; align=&quot;texttop&quot;></center><center><% Response.Write RS(&quot;HrsOff&quot / 60 & &quot; Hrs&quot;%></center></span>
<hr size=&quot;1&quot;>
<% RS2.MoveNext
RS.MoveNext
Loop
WEND
NEXT
%>
</tr>

Statement RS2 is the new query that comes from a different table, which has only user name and date.

Anyone can let me know what I do wrong, or another way to do this?
THANK YOU VERY MUCH.

 

[mwolf00]

Without disecting your code, I usually do this in a simpler manner...

do while.....
if bgColor = &quot;white&quot; then bgColor=&quot;silver&quot; else bgColor=&quot;white&quot;
response.write &quot;<tr bgcolor='&quot; & bgcolor & &quot;'>&quot;.....
loop

I only pay attention to what the current bgColor is and the I switch it....

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook

 

[Corneliu]

That is what i usually do when I display the data in rows, but this time is a little bit harder to do it that way, simply becuase I have to match the days from one table to another in the code itself.
I know the code looks a little busy, but here I removed the junk that is really not needed (pics and such).
When the days match from one table to the current record from the second table, than display it in red, otherwise display in white.

<tr>
<%
Dim workingDay, workingDate
arrWeekDays = Array(&quot;&quot;, &quot;Sunday&quot;, &quot;Monday&quot;, &quot;Tuesday&quot;, &quot;Wednesday&quot;, &quot;Thursday&quot;, &quot;Friday&quot;, &quot;Saturday&quot
Dim dtNow
dtNow = Now()
For workingDay = 1 to 7

workingDate = DateAdd(&quot;d&quot;,workingDay,WeekStart)
dtIterate = DateAdd(&quot;d&quot;, workingDay,WeekStart)
%>
<% While NOT RS2.EOF
If Day(RS2(&quot;ReqDate&quot) <> Day(workingDate) Then
Dim bgcolor
bgcolor =&quot;#FFFFFF&quot;
else
bgcolor =&quot;#FF0000&quot;
%>
<td bgcolor=&quot;<%= bgcolor %>&quot; valign=&quot;top&quot; width=&quot;14%&quot;>

<% Response.Write arrWeekDays(WeekDay(dtIterate))%>
<% Response.Write (FormatDateTime (workingDate, 2)) & &quot;<BR>&quot; %>
<% End If %>
<% Do Until RS.EOF
If Day(rs(&quot;ReqDate&quot) <> Day(workingDate) Then Exit Do %>
<% Response.Write (RS(&quot;Category_Name&quot)%><% Response.Write RS(&quot;HrsOff&quot / 60 & &quot; Hrs&quot;%>
<% RS2.MoveNext
RS.MoveNext
Loop
WEND
NEXT
%>
</tr>

 

[jackfl]

Perhaps:

If Rem(workingDate/2) = 1 Then
bgcolor='#ffffff'
Else
bgcolor='#ff0000'
End If

 

[GaryC123]

As Mwolf exampled just make this line one statement and remove the end if
If Day(RS2(&quot;ReqDate&quot) <> Day(workingDate) Then bgcolor = &quot;#FFFFFF&quot; else bgcolor =&quot;#FF0000&quot;

http://www.intouch-media.co.uk

 

[mwolf00]

If we could see some of the HTML output from the code, we may be able to dtermine where it's failing..

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook

 

[Corneliu]

GaryC I did what you said. The color for the day the user is late, it does come out red as it supposed to, but 2 things:
If the recordset is empty, now it gives me an error, so I have to use a If NOT RS2.EOF (or maybe I do something else wrong).
I looked at the source code after the page was displayed, and with the old code (where I just display the days with the categories), the table columns gets printed fine, but with the new code, the table only prints out 1 column.

This code, the table source I saw it displays ONLY 1 COLUMN:
<table COLS=&quot;7&quot; width=&quot;100%&quot; border=&quot;0&quot; cellpadding=&quot;0&quot; cellspacing=&quot;1&quot; bgcolor=&quot;#CCCCCC&quot; height=&quot;465&quot;>
<tr>
<%
Dim workingDay, workingDate
arrWeekDays = Array(&quot;&quot;, &quot;Sunday&quot;, &quot;Monday&quot;, &quot;Tuesday&quot;, &quot;Wednesday&quot;, &quot;Thursday&quot;, &quot;Friday&quot;, &quot;Saturday&quot
Dim dtNow
dtNow = Now()
For workingDay = 1 to 7

workingDate = DateAdd(&quot;d&quot;,workingDay,WeekStart)
dtIterate = DateAdd(&quot;d&quot;, workingDay,WeekStart)
%>
<%
Do Until RS2.EOF
If Day(RS2(&quot;ReqDate&quot) <> Day(workingDate) Then bgcolor = &quot;#FFFFFF&quot; else bgcolor =&quot;#FF0000&quot;
%>
<td bgcolor=&quot;#FFFFFF&quot; valign=&quot;top&quot; width=&quot;14%&quot;>
<% Response.Write arrWeekDays(WeekDay(dtIterate))%>
<% Response.Write (FormatDateTime (workingDate, 2)) & &quot;<BR>&quot; %>
<% Do Until RS.EOF
If Day(rs(&quot;ReqDate&quot) <> Day(workingDate) Then Exit Do %>
<span class=&quot;CalendarHours&quot;><% Response.Write (RS(&quot;Category_Name&quot)%><% Response.Write RS(&quot;HrsOff&quot / 60 & &quot; Hrs&quot;%></span>
<% RS2.MoveNext
RS.MoveNext
Loop
Loop
NEXT
%>
</tr>
</table>

With this code, when I view the source, it prints out all 7 columns, but I DO NOT use the color if statement. I hope this is not confusing.
<tr>
<%
Dim workingDay, workingDate
arrWeekDays = Array(&quot;&quot;, &quot;Sunday&quot;, &quot;Monday&quot;, &quot;Tuesday&quot;, &quot;Wednesday&quot;, &quot;Thursday&quot;, &quot;Friday&quot;, &quot;Saturday&quot
Dim dtNow
dtNow = Now()
For workingDay = 1 to 7

workingDate = DateAdd(&quot;d&quot;,workingDay,WeekStart)
dtIterate = DateAdd(&quot;d&quot;, workingDay,WeekStart)
%>
<td bgcolor#FFFFFF&quot; valign=&quot;top&quot; width=&quot;14%&quot;>

<% Response.Write arrWeekDays(WeekDay(dtIterate))%>
<% Response.Write (FormatDateTime (workingDate, 2)) & &quot;<BR>&quot; %>
<% End If %>
<% Do Until RS.EOF
If Day(rs(&quot;ReqDate&quot) <> Day(workingDate) Then Exit Do %>
<% Response.Write (RS(&quot;Category_Name&quot)%><% Response.Write RS(&quot;HrsOff&quot / 60 & &quot; Hrs&quot;%>
<% RS.MoveNext
Loop
NEXT
%>
</tr>

Anything I do wrong, or some other way to do this?

THANK YOU FOR YOUR HELP..

 

[GaryC123]

You seem to have some confusion in your Do loops, you have one Do loop based on RS2 then within that loop you have another Do Loop based on RS - within which you have 2 RS2.Movenext

http://www.intouch-media.co.uk

 

[Corneliu]

RS2 is the tardy database statement. RS is the calendar database statement.
the RS Table has the calendar time, category, etc.
the RS2 is the table where i basically enter the days when the user was late, tardy, etc.
I have them going at the same time because I need to match them. If the RS ReqDate = RS2 ReqDate than make the bgcolor red, else if empty, than just let it be white.

Tarwn helped me with the calendar a while back. I been trying for last few days to do this, but it seems that it wont work no matter which way I do it.
If I do not send the RS2 thru a loop, than it works like a charm, but of course I only get 1 day to be red, yet the database has more than 1 day for that week the user was late.

These are the sql statements:
Calendar Statement(RS):
SqlString = &quot;SELECT SUM(TimeFrame) as HrsOff, ScheduledTime.Technician, Technicians.UserID, ScheduledTime.Category, Category.Category as Category_Name, ScheduledTime.ReqDate &quot;
SqlString = SqlString & &quot; FROM (ScheduledTime INNER JOIN Category ON ScheduledTime.Category = Category.CatID) INNER JOIN Technicians ON ScheduledTime.Technician = Technicians.TechnicianID &quot;
SqlString = SqlString & &quot; WHERE ScheduledTime.ReqDate >= '&quot; & WeekStart & &quot;' AND ScheduledTime.ReqDate < '&quot; & DateAdd(&quot;d&quot;,7,WeekStart) & &quot;' AND Technicians.UserID = '&quot; & NTUserName & &quot;'&quot;
sqlString = sqlString & &quot; GROUP by ScheduledTime.ReqDate, ScheduledTime.Technician, Technicians.UserID, ScheduledTime.Category, Category.Category &quot;

The Tardy Statement(RS2):
SqlString = &quot;SELECT Tardy.ReqDate, Tardy.Technician &quot;
SqlString = SqlString & &quot; FROM Tardy INNER JOIN Technicians ON Tardy.Technician = Technicians.TechnicianIDD &quot;
SqlString = SqlString & &quot; WHERE Tardy.ReqDate >= '&quot; & WeekStart & &quot;' AND Tardy.ReqDate < '&quot; & DateAdd(&quot;d&quot;,7,WeekStart) & &quot;' AND Technicians.UserID = '&quot; & NTUserName & &quot;'&quot;

The last code I have
Again it all works good, but it only gives me 1 record, because I took it out of the loop.

<table COLS=&quot;7&quot; width=&quot;100%&quot; border=&quot;0&quot; cellpadding=&quot;0&quot; cellspacing=&quot;1&quot; bgcolor=&quot;#CCCCCC&quot; height=&quot;465&quot;>
<tr bgcolor=&quot;#FFFFFF&quot;>
<%
Dim workingDay, workingDate
arrWeekDays = Array(&quot;&quot;, &quot;Sunday&quot;, &quot;Monday&quot;, &quot;Tuesday&quot;, &quot;Wednesday&quot;, &quot;Thursday&quot;, &quot;Friday&quot;, &quot;Saturday&quot
Dim dtNow
dtNow = Now()
For workingDay = 1 to 7

workingDate = DateAdd(&quot;d&quot;,workingDay,WeekStart)
dtIterate = DateAdd(&quot;d&quot;, workingDay,WeekStart)
%>
<% 'While NOT RS2.EOF %>
<% If Day(RS2(&quot;ReqDate&quot) <> Day(workingDate) Then bgcolor = &quot;#FFFFFF&quot; else bgcolor =&quot;#FF0000&quot; %>
<% 'RS2.MoveNext
'Wend %><td bgcolor=&quot;<%= bgcolor %>&quot; valign=&quot;top&quot; width=&quot;14%&quot;>
<% Response.Write arrWeekDays(WeekDay(dtIterate))%>
<% Response.Write (FormatDateTime (workingDate, 2)) %>

<% Do Until RS.EOF %>
<% If Day(rs(&quot;ReqDate&quot) <> Day(workingDate) Then Exit Do %>
<% Response.Write (RS(&quot;Category_Name&quot)%><% Response.Write RS(&quot;HrsOff&quot / 60 & &quot; Hrs&quot;%>
<%
'RS2.MoveNext

RS.MoveNext
'Wend
Loop
NEXT
%>
</tr>
</table>

Anyone able to help me with this PLEASE?
THANK YOU ALL FOR YOUR HELP.