A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Crosstab Parameter Decleration



 
 
Thread Tools Display Modes
  #1  
Old July 9th, 2008, 07:42 PM posted to microsoft.public.access.queries
AccessARS
external usenet poster
 
Posts: 18
Default Crosstab Parameter Decleration

Hello,

I have been researching this for couple of days and with some trial and
error managed to build this croostab statement. Unfortunately my parameter
declaration gives me an error:

Invalid bracketing of Name '[Forms]!Overview![ExecDash]'

The purpose of this paramameter is to requery the crosstab on the LOB field
once the ExecDash unbound field value on the Overview form changes. Whether
it's populated or Null which is addressed by "Like *" in the where clause...

PARAMETERS [[Forms]!Overview![ExecDash]] Text ( 255 );
TRANSFORM Sum(qry_WF_HC_PART1.CountOfPernr) AS Total
SELECT qry_WF_HC_PART1.LOB
FROM qry_WF_HC_PART1
WHERE (((qry_WF_HC_PART1.LOB) Like "*" & [Forms]![Overview]![ExecDash]))
GROUP BY qry_WF_HC_PART1.LOB
PIVOT qry_WF_HC_PART1.EmployeeLevel;


Your input/direction would be greatly appreciated.

Thank you.
  #2  
Old July 9th, 2008, 08:02 PM posted to microsoft.public.access.queries
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Crosstab Parameter Decleration

You need to remove a pair of square brackets from around the PARAMETER. I
have experienced Access automatically adding these extra brackets and then
complaining. This has usually been when converting to a newer version.

PARAMETERS [Forms]!Overview![ExecDash] Text ( 255 );
TRANSFORM Sum(qry_WF_HC_PART1.CountOfPernr) AS Total
SELECT qry_WF_HC_PART1.LOB
FROM qry_WF_HC_PART1
WHERE (((qry_WF_HC_PART1.LOB) Like "*" & [Forms]![Overview]![ExecDash]))
GROUP BY qry_WF_HC_PART1.LOB
PIVOT qry_WF_HC_PART1.EmployeeLevel;

--
Duane Hookom
Microsoft Access MVP


"AccessARS" wrote:

Hello,

I have been researching this for couple of days and with some trial and
error managed to build this croostab statement. Unfortunately my parameter
declaration gives me an error:

Invalid bracketing of Name '[Forms]!Overview![ExecDash]'

The purpose of this paramameter is to requery the crosstab on the LOB field
once the ExecDash unbound field value on the Overview form changes. Whether
it's populated or Null which is addressed by "Like *" in the where clause...

PARAMETERS [[Forms]!Overview![ExecDash]] Text ( 255 );
TRANSFORM Sum(qry_WF_HC_PART1.CountOfPernr) AS Total
SELECT qry_WF_HC_PART1.LOB
FROM qry_WF_HC_PART1
WHERE (((qry_WF_HC_PART1.LOB) Like "*" & [Forms]![Overview]![ExecDash]))
GROUP BY qry_WF_HC_PART1.LOB
PIVOT qry_WF_HC_PART1.EmployeeLevel;


Your input/direction would be greatly appreciated.

Thank you.

  #3  
Old July 9th, 2008, 11:24 PM posted to microsoft.public.access.queries
AccessARS
external usenet poster
 
Posts: 18
Default Crosstab Parameter Decleration

Thank you...that seemed to resolve the issue.

"Duane Hookom" wrote:

You need to remove a pair of square brackets from around the PARAMETER. I
have experienced Access automatically adding these extra brackets and then
complaining. This has usually been when converting to a newer version.

PARAMETERS [Forms]!Overview![ExecDash] Text ( 255 );
TRANSFORM Sum(qry_WF_HC_PART1.CountOfPernr) AS Total
SELECT qry_WF_HC_PART1.LOB
FROM qry_WF_HC_PART1
WHERE (((qry_WF_HC_PART1.LOB) Like "*" & [Forms]![Overview]![ExecDash]))
GROUP BY qry_WF_HC_PART1.LOB
PIVOT qry_WF_HC_PART1.EmployeeLevel;

--
Duane Hookom
Microsoft Access MVP


"AccessARS" wrote:

Hello,

I have been researching this for couple of days and with some trial and
error managed to build this croostab statement. Unfortunately my parameter
declaration gives me an error:

Invalid bracketing of Name '[Forms]!Overview![ExecDash]'

The purpose of this paramameter is to requery the crosstab on the LOB field
once the ExecDash unbound field value on the Overview form changes. Whether
it's populated or Null which is addressed by "Like *" in the where clause...

PARAMETERS [[Forms]!Overview![ExecDash]] Text ( 255 );
TRANSFORM Sum(qry_WF_HC_PART1.CountOfPernr) AS Total
SELECT qry_WF_HC_PART1.LOB
FROM qry_WF_HC_PART1
WHERE (((qry_WF_HC_PART1.LOB) Like "*" & [Forms]![Overview]![ExecDash]))
GROUP BY qry_WF_HC_PART1.LOB
PIVOT qry_WF_HC_PART1.EmployeeLevel;


Your input/direction would be greatly appreciated.

Thank you.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.