[AccessD] Crosstab Query - Additional Parameter

So it looks like you need to define three parameters in the query design grid:

[Begin], Date/Time
[End], Date/Time
[cbosvcregion], Long, String ? Or whatever that ID data type is.

Define those and the crosstab should run when all three parameters are provided by the form. The problem is the region could be null (i.e. not a parameter the user wants to filter by), so you may in fact need to have two versions of this query, one that uses the region parameter and the other that does not. Your code would need to check if the user chose a region, and then make use of the relevant query version.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of RANDALL R ANTHONY
Sent: Friday, September 11, 2009 12:44 PM
To: 'Access Developers discussion and problem solving'
Cc: RANDALL R ANTHONY
Subject: Re: [AccessD] Crosstab Query - Additional Parameter

Here's the SQL:
 
TRANSFORM Count(TAB1.COMP_NUM) AS [The Value] SELECT Trim([TAB2].[LOB_DESC]) & " (" & Trim([TAB2].[LOB]) & ")" AS NETWORK, Count(TAB1.COMP_NUM) AS Total FROM (TAB2 INNER JOIN (TAB1 INNER JOIN RESOLUTION_TAB ON TAB1.COMP_NUM = RESOLUTION_TAB.COMP_NUM) ON TAB2.LOB = TAB1.NETWORK) LEFT JOIN SQLVIEW1 ON TAB1.COMP_NUM = SQLVIEW1.COMP_NUM
WHERE<<<(((RESOLUTION_TAB.MBR_SAT)="Y") AND ((TAB1.USERTYPE)=1) AND ((TAB1.COMP_DATE) Between [forms]![summ_rpt_shm_frm]![begin] And [forms]![summ_rpt_shm_frm]![End])>>> AND ***((SQLVIEW1.SVCREGCODEID)=[forms]![summ_rpt_shm_frm]![cbosvcregion]))***
GROUP BY Trim([TAB2].[LOB_DESC]) & " (" & Trim([TAB2].[LOB]) & ")"
ORDER BY Trim([TAB2].[LOB_DESC]) & " (" & Trim([TAB2].[LOB]) & ")"
PIVOT Format([COMP_DATE],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
The code between <> is static and will always have a value.  I'm trying to add a dynamic parameter between the **s.

>>> On 9/11/2009 at 10:17 AM, in message <B4F1446167F0BD4FAD640A79824EE0B00E1A51F8E0 at LIVP3MMBX11.mail.aig.net>, "Heenan, Lambert" <Lambert.Heenan at chartisinsurance.com> wrote:
Can you post the SQL? 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of RANDALL R ANTHONY
Sent: Friday, September 11, 2009 10:08 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Crosstab Query - Additional Parameter

Max,
I'm not getting that far, where I'm stuck at is what to put in the query.  If I put the field name in the query, Region as a group by, when I run the report the query is prompting me to enter the Region, ie, it doesn't recognize the table value.  I can't put the Region as a Where in the query grid.

>>> On 9/11/2009 at 9:51 AM, in message <cda8f54e0909110651y644b4f8frc6ced24c4c3a9e96 at mail.gmail.com>, Max Wanadoo <max.wanadoo at gmail.com> wrote:
Could you try this, perhaps:-

sWhere=nz(sWhere,"")
DoCmd.OpenReport sObjName, acViewPreview, , sWhere, , strSvcRegion & "~" & strLocality

This will ensure that sWhere is not null before the docmd is activated.

Max


On 9/11/09, RANDALL R ANTHONY <RRANTHON at sentara.com> wrote:
> Hi y'all,
> I've got a crosstab query that works great.  However, I need to pass 
> an additional parameter (that may or may not be null) and thought I 
> could just add them to the query grid.  This does not work.  I'm 
> assuming that it has something to do with not being part of the 
> columns displayed.  What do I need to do?
>
> DoCmd.OpenReport sObjName, acViewPreview, , sWhere, , strSvcRegion & 
> "~" & strLocality
>
> Is the command, sWhere = "" or 2
> --