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 all the classes to
show up on the same report. I can filter the query but the report won't
recognize the filtered query. I could post the entire code, but the filter
inside the report would look like this:
DoCmd.ApplyFilt er , "Me.courseC ode = '" &
Forms!frmSelect Course!cboSelec tCourse & "'"
or, in the filtered version of my query, my condition looks like this:
....WHERE
(((studentsInCo urses.courseCod e)=[Forms]![frmSelectCourse]![cboSelectCourse]
))
This WHERE condition works fine in the query but my report doesn't like it.
The report works fine in the unfiltered version of my query, but it shows
every class, thus creating empty columns.
In all the examples I've seen of dynamic crosstab query reports all over the
web I've never seen one that was filtered. Could it be that this type of
report just doesn't like filtering? Any ideas?
Many thanks in advance,
Rich Hollenbeck
Nov 13 '05
15  4402 
When you say you "make this query the recordsource of the report", do you
mean that you have opened the report in design view and written (or
selected) the name of the report in the RecordSource property of the
property sheet?
Or are you somehow doing this programmaticall y?
I've often seen the "Too few parameters" error when Access couldn't resolve
a parameter, but never when the recordset wasn't being constructed
separately using DAO.
HTH
"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in message
news:8x%Vd.7446 0$uc.57271@trnd dc04... Thanks, MacDermott, for your help. I'm sorry about moving your
posts to the bottom. I didn't mean to be anal, as somebody just
suggested, but I had recently been severely flamed about top
posting and I was attempting to correct the error of my ways.
You are exactly correct. The query runs correctly from the
database window, but when I make this query the recordsource of
the report, it returns no records. The main way I invoke this
report is by a menu item, which directly opens the report without
any filtering. Even when I open the report from the database
window there is no difference. I'm now getting an error that
says "Too few parameters. Expected 1."
Yes, the query is the RecordSource property in the property
sheet. That's one way I tried it. On another occasion I
programatically set the Me.RecordSource property in the
Form_Load() subroutine, but that didn't help either so I took it
out, just leaving it in the property sheet. Still, I use a
DAO.Recordset to dynamically assign field names in the report.
Again, this works perfectly in the report without the parameter
in the query, but it returns zero records with the parameter.
The query runs perfectly from the database window with or without
the parameter.
"MacDermott " <ma********@nos pam.com> wrote in message
news:Df******** *********@newsr ead2.news.atl.e arthlink.net... When you say you "make this query the recordsource of the
report", do you mean that you have opened the report in design view and written
(or selected) the name of the report in the RecordSource property
of the property sheet?
Or are you somehow doing this programmaticall y?
I've often seen the "Too few parameters" error when Access
couldn't resolve a parameter, but never when the recordset wasn't being
constructed separately using DAO.
HTH
"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in
message news:8x%Vd.7446 0$uc.57271@trnd dc04... Thanks, MacDermott, for your help. I'm sorry about moving
your posts to the bottom. I didn't mean to be anal, as somebody
just suggested, but I had recently been severely flamed about top
posting and I was attempting to correct the error of my ways.
You are exactly correct. The query runs correctly from the
database window, but when I make this query the recordsource
of the report, it returns no records. The main way I invoke
this report is by a menu item, which directly opens the report
without any filtering. Even when I open the report from the database
window there is no difference. I'm now getting an error that
says "Too few parameters. Expected 1."
Is this a form or a report?
You mostly talk about a report, but then you mention the Form_Load event.
I'm confused.
"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in message
news:LXYWd.8270 6$uc.78755@trnd dc04... Yes, the query is the RecordSource property in the property
sheet. That's one way I tried it. On another occasion I
programatically set the Me.RecordSource property in the
Form_Load() subroutine, but that didn't help either so I took it
out, just leaving it in the property sheet. Still, I use a
DAO.Recordset to dynamically assign field names in the report.
Again, this works perfectly in the report without the parameter
in the query, but it returns zero records with the parameter.
The query runs perfectly from the database window with or without
the parameter.
"MacDermott " <ma********@nos pam.com> wrote in message
news:Df******** *********@newsr ead2.news.atl.e arthlink.net... When you say you "make this query the recordsource of the
report", do you mean that you have opened the report in design view and written
(or selected) the name of the report in the RecordSource property
of the property sheet?
Or are you somehow doing this programmaticall y?
I've often seen the "Too few parameters" error when Access
couldn't resolve a parameter, but never when the recordset wasn't being
constructed separately using DAO.
HTH
"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in
message news:8x%Vd.7446 0$uc.57271@trnd dc04... Thanks, MacDermott, for your help. I'm sorry about moving your posts to the bottom. I didn't mean to be anal, as somebody just suggested, but I had recently been severely flamed about top
posting and I was attempting to correct the error of my ways.
You are exactly correct. The query runs correctly from the
database window, but when I make this query the recordsource of the report, it returns no records. The main way I invoke this report is by a menu item, which directly opens the report without any filtering. Even when I open the report from the database
window there is no difference. I'm now getting an error that
says "Too few parameters. Expected 1."
That's was a mistake. I work with more forms than reports. It
was probably a slip-up. I should have said something like
Report_Open.
"MacDermott " <ma********@nos pam.com> wrote in message
news:Vu******** *********@newsr ead2.news.atl.e arthlink.net... Is this a form or a report?
You mostly talk about a report, but then you mention the
Form_Load event. I'm confused.
OK, perhaps you can tell me more about this DAO recordset you're using...
"Richard Hollenbeck" <ri************ ****@verizon.ne t> wrote in message
news:TsBXd.3186 6$uc.19365@trnd dc09... That's was a mistake. I work with more forms than reports. It
was probably a slip-up. I should have said something like
Report_Open.
"MacDermott " <ma********@nos pam.com> wrote in message
news:Vu******** *********@newsr ead2.news.atl.e arthlink.net... Is this a form or a report?
You mostly talk about a report, but then you mention the
Form_Load event. I'm confused.
In this report, "lblHeader" is the activities' names above the
columns and "txtData" is the scores.
Thanks for hanging in there with me! The code follows:
Private Sub Report_Open(Can cel As Integer)
Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordse t("qryTableOfGr ades", dbOpenDynaset)
intColCount = rs.Fields.Count
intControlCount = Me.Detail.Contr ols.Count
If intControlCount < intColCount Then
intColCount = intControlCount
End If
' Fill in information for the necessary controls.
For i = 1 To intColCount
strName = rs.Fields(i - 1).Name
Me.Controls("lb lHeader" & i).Caption = strName
Me.Controls("tx tData" & i).ControlSourc e = strName
Next i
' Close the recordset.
rs.Close
End Sub
"MacDermott " <ma********@nos pam.com> wrote in message
news:w9******** *********@newsr ead2.news.atl.e arthlink.net... OK, perhaps you can tell me more about this DAO recordset
you're using... This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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
|
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...
|
by: Richard Hollenbeck |
last post by:
I have a crosstab query that shows all the scores of all the activities of
all the students in all courses, with the students being in the rows and the
activities being in the columns and the scores being in the intersections of
the rows and columns.
I also have a report based on this query that, depending on the activity
names, will dynamically insert the field names into the column labels, so
that I don't have to make a new report...
|
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:
|
by: deejayquai |
last post by:
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
| | |
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
|
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.
|
by: Peter Herath |
last post by:
I want to create a custormizable report . For an example, there's a form with four combo boxes and two of them having database tables columns/field names as values in the combo box(one for select row filed and other one for select column filed in the report) when u select items in that combo boxes and press a Button then selected items should go to the crosstab query as parameters and execute the query. upto that i have done my coding part but...
|
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,
|
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...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
