Crosstab/Parameters/Reports combo

[slb609]

I've gone almost blind reading all the different threads about this, and I just can't see my issue. Potentially stupidity, but most likely something very simple.

Setup:
I've a cross-tab query that uses a couple of regular feeder queries to build up what I want. The middle query has a parameter to limit it before it gets to the crosstab. I've declared the parameter in as many combinations as I can think of: Both the middle query and the crosstab, JUST the crosstab, and just the middle query. Either way it doesn't work when I open the report.

First query

SELECT Members.PKID AS shooterID, [members].[firstname] & " " & [members].[lastname] AS shooterName, MembershipType.MembershipType AS MembershipType, MembershipPayments.seasonpaid AS season, members.membertype as memberType
FROM MembershipType RIGHT JOIN (Members LEFT JOIN MembershipPayments ON Members.PKID = MembershipPayments.MemberID) ON MembershipType.PKID = Members.MemberType
UNION ALL select guests.GuestID as shooterID, guests.firstname & " " & guests.lastname as shooterName, "Guest" as MembershipType, guests.Season AS season, 4 AS memberType
from Guests;

that feeds into the middle query (query2):

PARAMETERS parm1 Short;
SELECT qCS.shooterName AS shooter, Meets.MeetDate, IIf(DLookUp("memberid","attendance","memberid = " & [qCS].[shooterID] & "and meetID = " & [meets].[pkid])=[qCS].[shooterID],True,False) AS attended, qCS.membershiptype AS membershiptype
FROM Meets, qryControlSheet AS qCS
WHERE (((qCS.season)=[parm1]) AND ((Year([MeetDate]))=[season]))

and then the crosstab:

PARAMETERS parm1 Short;
TRANSFORM First(Query2.attended) AS FirstOfattended
SELECT Query2.shooter, Query2.membershiptype
FROM Query2
GROUP BY Query2.shooter, Query2.membershiptype
PIVOT Query2.MeetDate;

If I execute the crosstab from the design window, I get a popup for parm1, enter the 4digit year and all is good: I get what I want.

If I open up the report, I get a runtime error 3601: Too few parameters. Expected 1. on the last line of code below:

<snip>
    seasonArg = PullOpenArgR(Me, 0)
    Me.seasonLabel.Caption = seasonArg
    
    strQuery_CT = "Query2_crosstab"
    strQuery_Inner = "Query2"

    strSQL = "PARAMETERS parm1 Short;" & _
            " TRANSFORM First(Query2.attended) AS FirstOfattended" & _
            " SELECT Query2.shooter, Query2.membershiptype" & _
            " FROM Query2" & _
            " GROUP BY Query2.shooter, Query2.membershiptype" & _
            " PIVOT Query2.MeetDate;
    Debug.Print strSQL
    CurrentDb.QueryDefs(strQuery_CT).Parameters("[parm1]") = seasonArg
    CurrentDb.QueryDefs(strQuery_CT).sql = strSQL
    Set Rs = CurrentDb.OpenRecordset(strSQL) 'This is the error
<snip>

This may be making things more complex because I'm putting the column headers in dynamically as per @isladogs fantastic post (that I can't post a link to), but it's failing before I get there, so I don't think that's the case.

I guess I have a couple of questions:

1. What stupidity am I up to? Do I need to declare the parm in both? Just in one? The one it's used or the crosstab? I've read that you've got to declare the parm in the crosstab, and it kinda makes sense to me that it gets pushed down into the middle SQL, but I'm just at a loss to figure this out.
   I swear I've tried every combination going, but I'm about to go ahead and give them all a go.
2. If I have my crosstab query already saved, do I need to rebuild it in the code? Can't I just do the querydef parameter thing and get the SQL to open as the recordset?

Here's my Immediate Window contents when I'm stopped in debug:

PARAMETERS parm1 Short; TRANSFORM First(Query2.attended) AS FirstOfattended SELECT Query2.shooter, Query2.membershiptype FROM Query2 GROUP BY Query2.shooter, Query2.membershiptype PIVOT Query2.MeetDate;

?strsql
PARAMETERS parm1 Short; TRANSFORM First(Query2.attended) AS FirstOfattended SELECT Query2.shooter, Query2.membershiptype FROM Query2 GROUP BY Query2.shooter, Query2.membershiptype PIVOT Query2.MeetDate;

?seasonarg
 2024 

?Currentdb.QueryDefs(strQuery_CT).Parameters.Count
 1 

?CurrentDb.QueryDefs(strQuery_CT).Parameters("[parm1]").Name
parm1

?Currentdb.QueryDefs(strQuery_CT).Parameters("[parm1]").value
<blank line - no value>

 

[arnelgp]

you can't just set the recordset of a Report (you can with Form, though).

 

[theDBguy]

I'm not sure but could you try replacing "Short" with "Text?"

 

[ebs17]

I have two suggestions:

1) Parameter passing and processing is much easier if you combine the three queries into one. This can be achieved by simply copy&paste.
It is easier to pass parameters to a single query, even to lower query levels.

2) The parameter passing as you use it inevitably leads to a recordset, which a report cannot do anything with. Therefore, a better alternative would be to use a public function per parameter. This function can be evaluated directly by Jet/ACE.

' in a standard module
Public Function GetParam1() As Long
   GetParam1 = Forms.frmXY.txtAnyField
End Function

TRANSFORM First(Query2.attended) AS FirstOfattended
SELECT Query2.shooter, Query2.membershiptype
FROM 

(SELECT qCS.shooterName AS shooter, Meets.MeetDate, IIf(DLookUp("memberid","attendance","memberid = " & [qCS].[shooterID] & "and meetID = " & [meets].[pkid])=[qCS].[shooterID],True,False) AS attended, qCS.membershiptype AS membershiptype
FROM Meets, 

(SELECT Members.PKID AS shooterID, [members].[firstname] & " " & [members].[lastname] AS shooterName, MembershipType.MembershipType AS MembershipType, MembershipPayments.seasonpaid AS season, members.membertype as memberType
FROM MembershipType RIGHT JOIN (Members LEFT JOIN MembershipPayments ON Members.PKID = MembershipPayments.MemberID) ON MembershipType.PKID = Members.MemberType
UNION ALL select guests.GuestID as shooterID, guests.firstname & " " & guests.lastname as shooterName, "Guest" as MembershipType, guests.Season AS season, 4 AS memberType
from Guests
) AS qCS

WHERE qCS.season = GetParam1() AND Year(MeetDate) = GetParam2()
) AS Query2

GROUP BY Query2.shooter, Query2.membershiptype
PIVOT Query2.MeetDate

I only copied them together and did not deal with the content of the queries themselves.

 

[Gasman]

Code Snippets

 

[Pat Hartman]

1. Domain functions in queries are very inefficient and there are always better methods.
2. Parameters should not "pop up". That will cause you to get multiple prompts under certain situations. Better practice is to use a form field reference or a TempVar
3. Crosstab queries REQUIRE parameters to be defined. No other query type requires this and that always leads to confusion. You need to define the parm in the query that uses it, and possibly in the Crosstab also (I'm not able to test this at the moment).
4. Unless you really like working in SQL view, there is no need to merge the queries. Access actually makes the merge when it is creating an execution plan for the top level query.

 

[MarkK]

CurrentDb.QueryDefs(strQuery_CT).Parameters("[parm1]") = seasonArg
CurrentDb.QueryDefs(strQuery_CT).sql = strSQL

Just guessing, but try reversing these two lines. If you set the SQL of a query, I would expect this automatically invalidates its previous parameters collection.

 

[DHookom]

Do you want consecutive dates (days, weeks, months, years) as column headings in a report? If so, consider using a text box on a form to capture the minimum or maximum value. Use a column heading based on the a short string and DateAdd() function referencing the text box on the form. These column headings calculate to values like Mth1, Mth2, Mth3,… This allows you to set the column headings property to these values you want to send to the report.

The report recordsource now has no-dynamic field names. You can generate column heading labels in the report with text boxes. The control sources will use DateAdd() with the interval and adding 1, 2, 3,…

No code, no parameters. Provide better specifications and I can possibly provide an example. This forum platform wouldn’t allow me to include a link to a better example.

 

[June7]

Here is tutorial on what DHookom described https://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/index.html
and his website has another http://www.access.hookom.net/Samples.htm

Duane, you should be able to post links.

 

[DHookom]

The link I attempted to post was to Tek-tips.com. I kept getting a message there was something unacceptable in my post.

Thanks for providing the links.

 

[June7]

Certainly common topic and discussions are everywhere. Here is one from Tek-tips https://www.tek-tips.com/threads/dynamic-crosstab-report.922818/
but why did you not post the Hook'd on Access source?

 

[DHookom]

That thread was from 20 years ago! The InvisibleInc.com link hasn't been available for about the same number of years.

 

[Gasman]

The link I attempted to post was to Tek-tips.com. I kept getting a message there was something unacceptable in my post.

Thanks for providing the links.

Duane,
You do not have sufficient posts to be able to post links. Jon upped the number a while back.

 

[DHookom]

Thanks for the explanation. I suppose the site wants to protect visitors from malicious links.

 

[Gasman]

Thanks for the explanation. I suppose the site wants to protect visitors from malicious links.

Yes, but the actual number before you can post, went up dramtically recently.

 

[DHookom]

I think there should be another method to qualify. I’ve been doing this for 25 years.

Any idea what the number of posts is?