CROSSTAB REPORT

  • Thread starter Thread starter George W. Jackson
  • Start date Start date
G

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
 
Possibly answered in another news group. Please try to keep your posts to
one news group.
 
Back
Top