RANDALL R ANTHONY
RRANTHON at sentara.com
Fri Sep 11 11:44:29 CDT 2009
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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com ( http://www.databaseadvisors.com/ ) (
> http://www.databaseadvisors.com/ )
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com ( http://www.databaseadvisors.com/ ) ( http://www.databaseadvisors.com/ )
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com ( http://www.databaseadvisors.com/ )
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com ( http://www.databaseadvisors.com/ )