473,852 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Dynamic Crosstab...SOS

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 lngRgColumnTota l(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
lngRgColumnTota l(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(C ancel 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(rstR eport(intX - 1))
Next intX

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

' Move to next record in recordset.
rstReport.MoveN ext
End If
End If

End Sub
Private Sub Detail_Print(Ca ncel 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.
lngRgColumnTota l(intX) = lngRgColumnTota l(intX) + Me("Col" +
Format(intX))
Next intX

' Put row total in text box in the "Detail" section.
Me("Col" + Format(intColum nCount + 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.MoveP revious

End Sub
Private Sub PageHeaderSecti on_Format(Cance l 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(intColum nCount + 1)) = "Totals"

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

End Sub
Private Sub Report_Close()

On Error Resume Next

' Close recordset.
rstReport.Close

End Sub
Private Sub Report_NoData(C ancel As Integer)

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

End Sub
Private Sub Report_Open(Can cel As Integer)

' Create underlying recordset for report using criteria entered in
' frmReportCommen ts 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!frmReport Comments
' Open QueryDef object.
Set qdf = dbsReport.Query Defs("qryDYNAMI CSUB")
' Set parameters for query based on values entered
' in frmReportCommen ts form.
qdf.Parameters( "Forms!frmRepor tComments![Report Date]") _
= frm![Report Date]
' Open Recordset object.
Set rstReport = qdf.OpenRecords et()

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

End Sub
Private Sub ReportFooter_Pr int(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)) = lngRgColumnTota l(intX)
Next intX

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

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

End Sub
Private Sub ReportHeader_Fo rmat(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.MoveF irst

'Initialize variables.
InitVars

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

Jan 20 '06 #1
2 2948
-----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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQ9FG1YechKq OuFEgEQJmCQCguu yMZaEIigpC/Y2/dVSpPu7T+D0AoJR O
utjRVY+EbZ9rBJC BqlRcaKvg
=KtFb
-----END PGP SIGNATURE-----

de********@gmai l.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 lngRgColumnTota l(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
lngRgColumnTota l(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(C ancel 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(rstR eport(intX - 1))
Next intX

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

' Move to next record in recordset.
rstReport.MoveN ext
End If
End If

End Sub
Private Sub Detail_Print(Ca ncel 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.
lngRgColumnTota l(intX) = lngRgColumnTota l(intX) + Me("Col" +
Format(intX))
Next intX

' Put row total in text box in the "Detail" section.
Me("Col" + Format(intColum nCount + 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.MoveP revious

End Sub
Private Sub PageHeaderSecti on_Format(Cance l 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(intColum nCount + 1)) = "Totals"

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

End Sub
Private Sub Report_Close()

On Error Resume Next

' Close recordset.
rstReport.Close

End Sub
Private Sub Report_NoData(C ancel As Integer)

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

End Sub
Private Sub Report_Open(Can cel As Integer)

' Create underlying recordset for report using criteria entered in
' frmReportCommen ts 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!frmReport Comments
' Open QueryDef object.
Set qdf = dbsReport.Query Defs("qryDYNAMI CSUB")
' Set parameters for query based on values entered
' in frmReportCommen ts form.
qdf.Parameters( "Forms!frmRepor tComments![Report Date]") _
= frm![Report Date]
' Open Recordset object.
Set rstReport = qdf.OpenRecords et()

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

End Sub
Private Sub ReportFooter_Pr int(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)) = lngRgColumnTota l(intX)
Next intX

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

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

End Sub
Private Sub ReportHeader_Fo rmat(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.MoveF irst

'Initialize variables.
InitVars

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

Jan 20 '06 #2
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
17691
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to Create a Dynamic Crosstab Report PRODUCT :Microsoft Access PROD/VER:1.00 1.10 OPER/SYS:WINDOWS
1
3345
by: Richard Hollenbeck | last post by:
Hello Newsgroup. You have all been very helpful in the past and I thank you. I try to ask relevant questions so that they don't just benefit me, but also benefit the group. I'm currently overwhelmed by useless examples across the web on how to make "dynamic crosstab reports" without myself having a basic understanding about how to retrieve and assign recordsources, etc., from fields in a query to fields in the report. I see all these...
15
4413
by: Richard Hollenbeck | last post by:
I tried to ask this question before on the 14th of January but I never got a reply. I'm still struggling with the problem. I'll try to rephrase the question: I have a crosstab query with rows of students and columns of activities and the data are the students' scores in each activity. No problem, almost. The problem is that there are five classes at the moment and will be more classes (or courses) in future semesters. I don't want...
3
3601
by: deejayquai | last post by:
Hi I've created a crosstab query and displayed it as a sub-report in my main report. This is fine until the data changes and the column names become incorrect. I know I have to create a 'dynamic crosstab query' but I don't know how to!! I've read the "How to..." on the Microsoft site but it mainly gives an example rather than explain the basics, which I can't work out. My context is:
1
4103
by: mtech1 | last post by:
Access 2002 I am trying to create a dynamic crosstab report that parameters come from 3 different forms. I get runtime error 3070 - The Microsoft Jet database engine does not recognize 'Forms!frmDefaults!ProviderID' as a valid field name or expression, and debug takes me to line 60 below. Any Suggestions Would Be Truly Appreciated!
4
2464
by: deejayquai | last post by:
I've worked through the MS KB example and it doesn't really help me as I missing the basics of what the code is doing. I've posted a couple of times here in thsi group but with no success. Could anyone recommend a book or a website that goes through the basics of how to build dynamic crosstab queries in order to create reports that allows column fields to dynamicaly change according to the source data? Regards David, UK
1
5169
by: Brad | last post by:
Thanks for taking the time to read my question. I have a table of data that has Date, Data and Category. I need to show, in a report, each Categories Data by Date. The Date has to be it's own column across the top and Category down the left side. As data is entered, the number of unique dates increases. As a result the
13
17173
by: salad | last post by:
Operating in A97. I didn't receive much of a response conserning Pivot tables in Access. Pivot tables are nice, but a CrossTab will work for me too. Using a Pivot table, one is actually launching Excel for data viewing. I'd prefer the user stay in Access. Creating dynamic crosstab queries is pretty simple. The problem is that the column count may shrink or grow depending on the filter.
14
7871
ollyb303
by: ollyb303 | last post by:
Hi, I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor. I have one crosstab query (Query1) which has the following fields: SPID (supervisor ID), total:group by, as row heading Date, total:group by, as column heading Calls handled, total:sum, as value Date, total:where, criteria between and - this is taken from a form,
0
9898
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9748
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10670
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10735
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10356
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9506
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7907
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5736
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4143
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.