Help with dynamic crosstab query report

[puttergirl]

I'm creating a report using the code in this article:

http://support.microsoft.com/kb/328320

My crosstab query looks like this:
TRANSFORM First([2 yr hx].[S/S]) AS [FirstOfS/S]
SELECT [2 yr hx].EXIT_DATE AS [EXIT DATE], Count([2 yr hx].[S/S]) AS CITATIONS
FROM [2 yr hx]
GROUP BY [2 yr hx].EXIT_DATE
ORDER BY [2 yr hx].EXIT_DATE DESC
PIVOT [2 yr hx].TAG;


I need to modify it so that the columns in the Details Section of the report have the Value FirstOfS/S instead of a count (as in the below code). Can someone tell me how to do that? The values in the FirstOfS/S field are just 1 character length, A through L.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer
Dim lngRowTotal As Long

' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0

For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
Next intX

' Put row total in text box in the "Detail" section.
Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
End If
End Sub

 

[dhookom]

I don't care much for the code in the link provided. It is slow and inflexible. I recommend the crosstab download at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

However, I don't know what you are attempting especially since "first" isn't at all reliable.

Duane
Hook'D on Access
MS Access MVP