crosstab query and report (Access 2000 all updates)

Topic

New Reply

I am trying to do a report based on a crosstab query. SInce the column names aren’t known prior to formatting, I found code (courtesty of Getz, Litwin and Gilbert in Access 2000 Developers Handbook) which will produce a report based on a crosstab query…until Parameters enter into the process.

Below is my crosstab query SQL.
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Count(tblVolunteerSchedule.VolDate) AS CountOfVolDate
SELECT tblVolunteers.LastName, tblVolunteers.FirstName
FROM tblVolunteers LEFT JOIN tblVolunteerSchedule ON tblVolunteers.VolunteerID = tblVolunteerSchedule.VolunteerID
WHERE (((tblVolunteerSchedule.VolDate) Between [Start Date] And [End Date]))
GROUP BY tblVolunteers.LastName, tblVolunteers.FirstName
PIVOT tblVolunteerSchedule.VolDate;

The crosstab query runs fine.

The report works fine if there are no parameters. But as soon as parameters are part of the report source, the report shows completely blank.

Below is the code for the report…
Private Sub Report_Open(Cancel As Integer)
Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

On Error Resume Next

Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rst = db.OpenRecordset(“Query1”)

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

If intControlCount < intColCount Then
intColCount = intControlCount
End If

For i = 1 To intColCount
strName = rst.Fields(i – 1).Name
Me.Controls("lblHeader" & i).Caption = strName
Me.Controls("txtData" & i).ControlSource = strName
Next i

For i = intColCount + 1 To intControlCount
Me.Controls("txtData" & i).Visible = False
Me.Controls("lblHeader" & i).Visible = False
Next i
rst.Close
End Sub

I know that crosstab reports are pesky at best, but I have worked at this for so long that I don't know which way to turn to make it come out right.

Why does the introduction of a parameter make the report go flooey? Is there a way to fix it?

Tom

Reply | Quote

Replies

The problem with opening recordsets on parameter queries is that you have to explicitly specify the parameter values for the query and then execute it.

I’d be tempted to change your code to something like

Private Sub Report_Open(Cancel As Integer)
Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

Dim myStr as string

On Error Resume Next

Dim rst As DAO.Recordset
Dim db As DAO.Database

mystr = “TRANSFORM Count(tblVolunteerSchedule.VolDate) AS CountOfVolDate”
mystr = mystr & “SELECT tblVolunteers.LastName, tblVolunteers.FirstName”
mystr = mystr & “FROM tblVolunteers LEFT JOIN tblVolunteerSchedule ON tblVolunteers.VolunteerID = tblVolunteerSchedule.VolunteerID”
mystr = mystr & “WHERE (((tblVolunteerSchedule.VolDate) Between #”
mystr = mystr & format([Start Date],”mm/dd/yyyy”) & “# And #” & format([End Date],”mm/dd/yyyy”) & “#))”
mystr = mystr & “GROUP BY tblVolunteers.LastName, tblVolunteers.FirstName”
mystr = mystr & “PIVOT tblVolunteerSchedule.VolDate;”

Set db = CurrentDb
Set rst = db.OpenRecordset(mystr)

This will generate the query for you. You will have to replace the [StartDate] and [EndDate] with valid fields or values.

otherwise have a look in the help for using the querydef object

eg

Set rst = query1.openrecordset

Reply | Quote

The problem with opening recordsets on parameter queries is that you have to explicitly specify the parameter values for the query and then execute it.

I’d be tempted to change your code to something like

Private Sub Report_Open(Cancel As Integer)
Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String

Dim myStr as string

On Error Resume Next

Dim rst As DAO.Recordset
Dim db As DAO.Database

mystr = “TRANSFORM Count(tblVolunteerSchedule.VolDate) AS CountOfVolDate”
mystr = mystr & “SELECT tblVolunteers.LastName, tblVolunteers.FirstName”
mystr = mystr & “FROM tblVolunteers LEFT JOIN tblVolunteerSchedule ON tblVolunteers.VolunteerID = tblVolunteerSchedule.VolunteerID”
mystr = mystr & “WHERE (((tblVolunteerSchedule.VolDate) Between #”
mystr = mystr & format([Start Date],”mm/dd/yyyy”) & “# And #” & format([End Date],”mm/dd/yyyy”) & “#))”
mystr = mystr & “GROUP BY tblVolunteers.LastName, tblVolunteers.FirstName”
mystr = mystr & “PIVOT tblVolunteerSchedule.VolDate;”

Set db = CurrentDb
Set rst = db.OpenRecordset(mystr)

This will generate the query for you. You will have to replace the [StartDate] and [EndDate] with valid fields or values.

otherwise have a look in the help for using the querydef object

eg

Set rst = query1.openrecordset

Reply | Quote

Using parameters such as [Start Date] require the user to enter a value interactively. It is difficult to get at this value programmatically. You should create a form with text boxes in which the user can enter the start date and end date, you can then use the values of these text boxes in your code. See for example post 248210 or post 155586 (both are in Access 97 format, but will work after converting to Access 2000 format)

Reply | Quote

Stewart
The report still showed up blank. But thanks for sending a possible solution.

Hans
I downloaded the first post you suggested, and will have a look at it.

Thanks.
Tom

Reply | Quote

Hans
With a little tweaking, your code worked wonderfully. I wanted to “sum” some of the columns, so had to work out some additional code to do that.

In case anyone is interested, here is what I got to work.

Private Sub Report_Open(Cancel As Integer)
Dim intX As Integer
Dim i As Integer
Dim intColCount As Integer
Dim intControlCount As Integer
Dim strName As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim lngErr As Long

On Error GoTo ErrHandler

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(“Query1”)
‘ Set parameters for query based on values entered
‘ in frmPayDays form.
qdf.Parameters(“Forms!Form1!txtStartDate”) _
= Forms!Form1!txtStartDate
qdf.Parameters(“Forms!Form1!txtEndDate”) _
= Forms!Form1!txtEndDate

‘ Open Recordset object.
Set rst = qdf.OpenRecordset()

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

If intControlCount < intColCount Then
intColCount = intControlCount
End If

For i = 1 To intColCount
strName = rst.Fields(i – 1).Name
Me.Controls("lblHeader" & i).Caption = strName
If IsDate(Me.Controls("lblHeader" & i).Caption) Then
Me.Controls("lblHeader" & i).Caption = "Present on" & vbCrLf & strName
End If
Me.Controls("txtData" & i).ControlSource = strName
Next i
For intX = 3 To intColCount
strName = rst.Fields(intX – 1).Name
Me.Controls("txtSum" & intX).ControlSource = "=Sum([" & strName & "])"
Next intX

For i = intColCount + 1 To intControlCount
Me.Controls("txtData" & i).Visible = False
Me.Controls("lblHeader" & i).Visible = False
For intX = intColCount + 3 To intControlCount
Me.Controls("txtSum" & intX).Visible = False
Next intX
Next i

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

Thanks again for your help.

Tom

Reply | Quote

I’m glad it worked out; crosstab reports usually require a fair amount of tweaking.

Reply | Quote

I’m glad it worked out; crosstab reports usually require a fair amount of tweaking.

Reply | Quote

Hans
With a little tweaking, your code worked wonderfully. I wanted to “sum” some of the columns, so had to work out some additional code to do that.

In case anyone is interested, here is what I got to work.

Private Sub Report_Open(Cancel As Integer)
Dim intX As Integer
Dim i As Integer
Dim intColCount As Integer
Dim intControlCount As Integer
Dim strName As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim lngErr As Long

On Error GoTo ErrHandler

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(“Query1”)
‘ Set parameters for query based on values entered
‘ in frmPayDays form.
qdf.Parameters(“Forms!Form1!txtStartDate”) _
= Forms!Form1!txtStartDate
qdf.Parameters(“Forms!Form1!txtEndDate”) _
= Forms!Form1!txtEndDate

‘ Open Recordset object.
Set rst = qdf.OpenRecordset()

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

If intControlCount < intColCount Then
intColCount = intControlCount
End If

For i = 1 To intColCount
strName = rst.Fields(i – 1).Name
Me.Controls("lblHeader" & i).Caption = strName
If IsDate(Me.Controls("lblHeader" & i).Caption) Then
Me.Controls("lblHeader" & i).Caption = "Present on" & vbCrLf & strName
End If
Me.Controls("txtData" & i).ControlSource = strName
Next i
For intX = 3 To intColCount
strName = rst.Fields(intX – 1).Name
Me.Controls("txtSum" & intX).ControlSource = "=Sum([" & strName & "])"
Next intX

For i = intColCount + 1 To intControlCount
Me.Controls("txtData" & i).Visible = False
Me.Controls("lblHeader" & i).Visible = False
For intX = intColCount + 3 To intControlCount
Me.Controls("txtSum" & intX).Visible = False
Next intX
Next i

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

Thanks again for your help.

Tom

Reply | Quote

Stewart
The report still showed up blank. But thanks for sending a possible solution.

Hans
I downloaded the first post you suggested, and will have a look at it.

Thanks.
Tom

Reply | Quote