-----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-----
deejayquai@gmail.com wrote:[color=blue]
> 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
>
>
> ----------------------
>[/color]
Dynamic Crosstab...SOS 
