Looping through Cross-tab query recordset

[BananaQuaaludes]

I am using the following cross-tab query to form a recordset:

strSQL = "TRANSFORM Max(tblCrossTab.Hours) AS MaxOfHours SELECT tblCrossTab.EmpName, Sum(tblCrossTab.Hours) AS [Total Of Hours] FROM tblCrossTab GROUP BY tblCrossTab.EmpName PIVOT tblCrossTab.Month;"

How do I loop through the contents of that query to populate an HTML table? The following code produces the "Item cannot be found in the collection corresponding to the requested name or ordinal" error on the "MaxOfHours" line:

<% do until RS.EOF %>
<tr>
<td><%=RS(&quot;EmpName&quot%></td>
<td><%=RS(&quot;MaxOfHours&quot%></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<% RS.MoveNext
loop

I've tried referencing the other fields in the query, but the only field I can get out of the recordset is the EmpName. Do I need to change the query, or is the data there but I'm not referencing it correctly?

Thanks!

 

[dhookom]

You can create column headings by stepping through the fields collection (you will need to add the formatting and tags as I am not that familiar with the tag syntax)

Dim fld
'make these <th>
For each fld in rs.Fields
fld.Name
Next
'then loop through the records and field
Dim intLoop
Do until rs.eof
'create your <tr>
For intLoop = 0 to rs.Fields.Count -1
'this is a <td>
rs.Fields(intLoop)
Next
rs.MoveNext
Loop