By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,682 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,682 IT Pros & Developers. It's quick & easy.

Dynamic Crosstab...SOS

P: n/a
Hi

I'm trying to produce a report based on a dynamic crosstab. Ultimately
i'd like the report to actually become a sub report within a student
end of year record of achievement. The dynamic sub-report will capture
what grades the student has achieved in a list of different subjects
and the reason I need it to be dynamic is that students take different
subjects.

Basically I've been trying to doctor the KB article on dynamic
crosstabs for the Northwind Database just so that I can produce a
dynamic report, I haven't got to the sub-report bit yet. I've created
a crosstab query list the grades per subject that uses a where
parameter for a report date field in a separate form- just like the
example. This seems fine.

I've created a report with 11 headings/columns and set up a command
click to open the report from a form that contains the date- again just
like the example. But I don't think this is right as don't need 11
columns or to total anything. i hust need to be able to dynamically
change the column headings according to the list of subjects a student
is taking. I've altered what I think are the references within the
code for the report to my own forms and queries but this is where my
knowledge falls down. Using the code below I'm getting an "error code
13- Type Mis Match" with the de-bugger highlighting:

lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

- as the culprit! I would be so grateful to anyone who could help me
with this, I've been struggling to do this for over a year now, plus if
I can get this report to work, I then need to nest it as a sub-report
in the main end of year achievement report. I'll gladly provide more
detailed information if any body shows an interest in helping me with
this.

David, UK

The full code from the report is listed below.
---------------------

Option Compare Database

' Constant for maximum number of columns qryDYNAMICSUB query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 11

' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset

' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub InitVars()

Dim intX As Integer

' Initialize lngReportTotal variable.
lngReportTotal = 0

' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX

End Sub
Private Function xtabCnulls(varX As Variant)

' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If

End Function
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX

' Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub
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
Private Sub Detail_Retreat()

' Always back up to previous record when "Detail" section retreats.
rstReport.MovePrevious

End Sub
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)

Dim intX As Integer

' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).name
Next intX

' Make next available text box Totals heading.
Me("Head" + Format(intColumnCount + 1)) = "Totals"

' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX

End Sub
Private Sub Report_Close()

On Error Resume Next

' Close recordset.
rstReport.Close

End Sub
Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records match the criteria you entered.", vbExclamation,
"No Records Found"
rstReport.Close
Cancel = True

End Sub
Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria entered in
' frmReportComments form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!frmReportComments
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("qryDYNAMICSUB")
' Set parameters for query based on values entered
' in frmReportComments form.
qdf.Parameters("Forms!frmReportComments![Report Date]") _
= frm![Report Date]
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

End Sub
Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As
Integer)

Dim intX As Integer

' Put column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab value).
For intX = 2 To intColumnCount
Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
Next intX

' Put grand total in text box in report footer.
Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("Tot" + Format(intX)).Visible = False
Next intX

End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)

' Move to first record in recordset at the beginning of the report
' or when the report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
rstReport.MoveFirst

'Initialize variables.
InitVars

End Sub
----------------------

Jan 20 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The Format() should be like this:

Format(intX,"00")

The type mismatch error may mean the value of the control is not a
numeric value. Try the & string concatenation symbol instead of the
plus sign.

lngRowTotal = lngRowTotal + Me("Col" & Format(intX,"00"))
To get a zero in place of a NULL use the Nz() function in place of the
call to the xtabCnulls function:

Nz(value,0) or Nz(value)

You can even replace the NULL with whatever you want, numeric or alpha:

Nz(value,"That wasn't what I wanted")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9FG1YechKqOuFEgEQJmCQCguuyMZaEIigpC/Y2/dVSpPu7T+D0AoJRO
utjRVY+EbZ9rBJCBqlRcaKvg
=KtFb
-----END PGP SIGNATURE-----

de********@gmail.com wrote:
Hi

I'm trying to produce a report based on a dynamic crosstab. Ultimately
i'd like the report to actually become a sub report within a student
end of year record of achievement. The dynamic sub-report will capture
what grades the student has achieved in a list of different subjects
and the reason I need it to be dynamic is that students take different
subjects.

Basically I've been trying to doctor the KB article on dynamic
crosstabs for the Northwind Database just so that I can produce a
dynamic report, I haven't got to the sub-report bit yet. I've created
a crosstab query list the grades per subject that uses a where
parameter for a report date field in a separate form- just like the
example. This seems fine.

I've created a report with 11 headings/columns and set up a command
click to open the report from a form that contains the date- again just
like the example. But I don't think this is right as don't need 11
columns or to total anything. i hust need to be able to dynamically
change the column headings according to the list of subjects a student
is taking. I've altered what I think are the references within the
code for the report to my own forms and queries but this is where my
knowledge falls down. Using the code below I'm getting an "error code
13- Type Mis Match" with the de-bugger highlighting:

lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

- as the culprit! I would be so grateful to anyone who could help me
with this, I've been struggling to do this for over a year now, plus if
I can get this report to work, I then need to nest it as a sub-report
in the main end of year achievement report. I'll gladly provide more
detailed information if any body shows an interest in helping me with
this.

David, UK

The full code from the report is listed below.
---------------------

Option Compare Database

' Constant for maximum number of columns qryDYNAMICSUB query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 11

' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset

' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub InitVars()

Dim intX As Integer

' Initialize lngReportTotal variable.
lngReportTotal = 0

' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX

End Sub
Private Function xtabCnulls(varX As Variant)

' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If

End Function
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX

' Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub
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
Private Sub Detail_Retreat()

' Always back up to previous record when "Detail" section retreats.
rstReport.MovePrevious

End Sub
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)

Dim intX As Integer

' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).name
Next intX

' Make next available text box Totals heading.
Me("Head" + Format(intColumnCount + 1)) = "Totals"

' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX

End Sub
Private Sub Report_Close()

On Error Resume Next

' Close recordset.
rstReport.Close

End Sub
Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records match the criteria you entered.", vbExclamation,
"No Records Found"
rstReport.Close
Cancel = True

End Sub
Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria entered in
' frmReportComments form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!frmReportComments
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("qryDYNAMICSUB")
' Set parameters for query based on values entered
' in frmReportComments form.
qdf.Parameters("Forms!frmReportComments![Report Date]") _
= frm![Report Date]
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

End Sub
Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As
Integer)

Dim intX As Integer

' Put column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab value).
For intX = 2 To intColumnCount
Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
Next intX

' Put grand total in text box in report footer.
Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("Tot" + Format(intX)).Visible = False
Next intX

End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)

' Move to first record in recordset at the beginning of the report
' or when the report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
rstReport.MoveFirst

'Initialize variables.
InitVars

End Sub
----------------------

Jan 20 '06 #2

P: n/a
Thanks for your reply- but I now get an error- "Can't find the field
Col02' referred to in your expression.

Regards

David, UK

Jan 23 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.