Parameter in Crosstab Query

[ryguy7272]

I found a few articles online that said a persona can use a parameter inside
of a crosstab query, so I know it can be done, but I can’t figure out how to
do it for my specific query. I guess it is run from a Form, right.

I have a Form named ‘frmExport’ that runs a couple dozen macros to make sure
all queries are updated before three queries (which get all information from
these other queries) are exported as Excel spreadsheets. How can I pass a
parameter to the this one query (SQL below) when this specific query is
called by the Macro and updated?

TRANSFORM Sum(tblJolly.Revenue) AS SumOfRevenue
SELECT tblVP_Mapping.VP, tblVP_Mapping.[Sales Rep], tblJolly.[Related
Company], tblJolly.Company, tblJolly.Opportunity, "N" AS Quarter
FROM tblJolly INNER JOIN tblVP_Mapping ON tblJolly.[Sales Rep] =
tblVP_Mapping.[JOLIET ID]
WHERE (((tblJolly.Period) Between #7/1/2008# And #9/30/2008#))
GROUP BY tblVP_Mapping.VP, tblVP_Mapping.[Sales Rep], tblJolly.[Related
Company], tblJolly.Company, tblJolly.Opportunity, "N"
PIVOT tblJolly.InventoryType;


Regards,
Ryan---

 

[Allen Browne]

Either declare the parameter, or specify the column heads.

Details for both approaches in:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html

 

[ryguy7272]

Well, well, well, your site was one of the online resources that I was
looking at Allen! Thanks for taking the time to post your suggestion, but I
am still not getting it to work. I added this to the first line of the SQL:
PARAMETERS [Period] DateTime,[Period] DateTime;

That doesn't do anything at all.

The Column Heading is 'Period'. How do I specify that in the SQL?


Regards,
Ryan---

--
RyGuy

Either declare the parameter, or specify the column heads.

Details for both approaches in:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I found a few articles online that said a persona can use a parameter
inside
of a crosstab query, so I know it can be done, but I can’t figure out how
to
do it for my specific query. I guess it is run from a Form, right.

I have a Form named ‘frmExport’ that runs a couple dozen macros to make
sure
all queries are updated before three queries (which get all information
from
these other queries) are exported as Excel spreadsheets. How can I pass a
parameter to the this one query (SQL below) when this specific query is
called by the Macro and updated?

TRANSFORM Sum(tblJolly.Revenue) AS SumOfRevenue
SELECT tblVP_Mapping.VP, tblVP_Mapping.[Sales Rep], tblJolly.[Related
Company], tblJolly.Company, tblJolly.Opportunity, "N" AS Quarter
FROM tblJolly INNER JOIN tblVP_Mapping ON tblJolly.[Sales Rep] =
tblVP_Mapping.[JOLIET ID]
WHERE (((tblJolly.Period) Between #7/1/2008# And #9/30/2008#))
GROUP BY tblVP_Mapping.VP, tblVP_Mapping.[Sales Rep], tblJolly.[Related
Company], tblJolly.Company, tblJolly.Opportunity, "N"
PIVOT tblJolly.InventoryType;


Regards,
Ryan---

 

[Allen Browne]

From your SQL statement, I think you want the query to pop up parameter
boxes to ask you for the starting date and the ending date to apply against
the Period column?

If so, Period is not the parameter name - it's a field name. You need to
create a couple more names for the parameters.

Try something like this:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
TRANSFORM Sum(tblJolly.Revenue) AS SumOfRevenue
SELECT tblVP_Mapping.VP,
tblVP_Mapping.[Sales Rep],
tblJolly.[Related Company],
tblJolly.Company,
tblJolly.Opportunity, "N" AS Quarter
FROM tblJolly INNER JOIN tblVP_Mapping
ON tblJolly.[Sales Rep] = tblVP_Mapping.[JOLIET ID]
WHERE (tblJolly.Period Between [StartDate] And [EndDate])
GROUP BY tblVP_Mapping.VP,
tblVP_Mapping.[Sales Rep],
tblJolly.[Related Company],
tblJolly.Company,
tblJolly.Opportunity,
"N"
PIVOT tblJolly.InventoryType;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Well, well, well, your site was one of the online resources that I was
looking at Allen! Thanks for taking the time to post your suggestion, but
I
am still not getting it to work. I added this to the first line of the
SQL:
PARAMETERS [Period] DateTime,[Period] DateTime;

That doesn't do anything at all.

The Column Heading is 'Period'. How do I specify that in the SQL?


Regards,
Ryan---

--
RyGuy

Either declare the parameter, or specify the column heads.

Details for both approaches in:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html

 

[ryguy7272]

It worked!!

I guess it was this:
WHERE (((tblJolly.Period) Between #7/1/2008# And #9/30/2008#))

which should have been this:
WHERE (tblJolly.Period Between [StartDate] And [EndDate])

and the variables come from here:
PARAMETERS [StartDate] DateTime, [EndDate] DateTime;

Finally, for others reading this sometime in the future, remember this: in
Design View, click 'Query' and 'Parameters' and then under Parameter, input
'[StartDate]' and under Data Type, input Date/Time, then in the next row,
under Parameter, input '[EndDate]' and under Data Type, input Date/Time

Thanks for everything Allen!!

Regards,
Ryan---

--
RyGuy

From your SQL statement, I think you want the query to pop up parameter
boxes to ask you for the starting date and the ending date to apply against
the Period column?

If so, Period is not the parameter name - it's a field name. You need to
create a couple more names for the parameters.

Try something like this:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
TRANSFORM Sum(tblJolly.Revenue) AS SumOfRevenue
SELECT tblVP_Mapping.VP,
tblVP_Mapping.[Sales Rep],
tblJolly.[Related Company],
tblJolly.Company,
tblJolly.Opportunity, "N" AS Quarter
FROM tblJolly INNER JOIN tblVP_Mapping
ON tblJolly.[Sales Rep] = tblVP_Mapping.[JOLIET ID]
WHERE (tblJolly.Period Between [StartDate] And [EndDate])
GROUP BY tblVP_Mapping.VP,
tblVP_Mapping.[Sales Rep],
tblJolly.[Related Company],
tblJolly.Company,
tblJolly.Opportunity,
"N"
PIVOT tblJolly.InventoryType;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Well, well, well, your site was one of the online resources that I was
looking at Allen! Thanks for taking the time to post your suggestion, but
I
am still not getting it to work. I added this to the first line of the
SQL:
PARAMETERS [Period] DateTime,[Period] DateTime;

That doesn't do anything at all.

The Column Heading is 'Period'. How do I specify that in the SQL?


Regards,
Ryan---

--
RyGuy

Either declare the parameter, or specify the column heads.

Details for both approaches in:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html

I found a few articles online that said a persona can use a parameter
inside of a crosstab query, so I know it can be done, but I can’t
figure
out how to do it for my specific query. I guess it is run from a Form,
right.

I have a Form named ‘frmExport’ that runs a couple dozen macros to
make sure all queries are updated before three queries (which get all
information from these other queries) are exported as Excel
spreadsheets. How can I pass a parameter to the this one query
(SQL below) when this specific query is called by the Macro and
updated?