Crosstab Report

[George W. Jackson]

I have a crosstab query that is being put into an excel spreadsheet using
this code:

Private Sub crosstab_Click()
DoCmd.OutputTo acOutputQuery, "crosstab", acFormatXLS, "D:\test.xls", True
End Sub


In the crosstab I have three columns:

ONE: EMPLOYEE (Table: projectfrydays; Total: Group By; Crosstab: Row
Heading)
TWO: FRYDAY (Table: projectfrydays; Total: Group By; Crosstab: Column
Heading)
THREE: FUNCTION: LAST(FILLFRYDAY(FRYDAY), (EMPLOYEENAME)) (Total:
Expression; Crosstab: Value)


Notice in the third column a function is being used. This function
basically allows for more then one entry in each cell of the spreadsheet. I
don't want the spreadsheet! I would love for the results of this query to
be in a report or have a report do the work instead of even having this
query. The function code is posted below. Thanks in advance for any
suggestions.


Public Function FillFryday(selFryday As Date, selEmp As Integer) As String
'Dim dbs As Database, rst As Recordset

Dim recnum, i, j As Integer
Dim SQLSTRING As String, DATESTRING As String, fillstring As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset



If IsNull(selFryday) Then GoTo EndF:
DATESTRING = Month(selFryday) & "/" & Day(selFryday) & "/" &
Year(selFryday)
SQLSTRING = "SELECT DISTINCT projectfrydays.project FROM projectfrydays
WHERE ((fryday = #" & DATESTRING & "# AND employeeID = " & selEmp & " ));"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(SQLSTRING)
rst.MoveLast
rst.MoveFirst
recnum = rst.RecordCount
'MsgBox recnum
fillstring = rst.Fields(0)
If recnum = 1 Then GoTo Cont01
For i = 1 To recnum - 1
rst.MoveNext
fillstring = fillstring + Chr$(10) + rst.Fields(0)
Next i

Cont01:
FillFryday = fillstring
rst.Close
Set dbs = Nothing

EndF:
End Function

 

[Duane Hookom]

I have posted similar (but generic) code to your FillFryDay() function many
times. The main difference between our code is that I loop until rst.EOF. I
expect you could use this with an Access report if you new ahead of time
what your Column Headings were going to be. You could then enter these into
the Column Headings property of the Crosstab. Not knowing your data or what
you are storing in FryDay, I can't provide a better answer. There is a
sample at http://www.invisibleinc.com/divFiles.cfm?divDivID=4 showing how to
create dynamic crosstab reports.