Odd Parameter request in Crosstab

  • Thread starter Thread starter AccessKay
  • Start date Start date
A

AccessKay

Good Morning,

I read the threads about creating parameters in crosstab queries and this
helped me to get it to work but the parameter box pops up when I try to save
the query. Though my changes are saved, I don’t think this is normal? It
happens in all views. Can anyone tell me what might be wrong? Here is my
SQL:

PARAMETERS [Enter Month and Year] DateTime;
TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
FROM Trans_Mstr_ODC
WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
Trans_Mstr_ODC.Product
PIVOT Trans_Mstr_ODC.ODC_Cost_Category;

TIA,
Kay
 
Crosstabs can be a little buggy with parameters. Your best bet is to create a
normal select query that includes the declared parameter and any other
criteria to gather up the records and fields that you want to see. Make sure
that this query works then save it with a name. Next create a crosstab query
using the first query as its record source.
 
Thank you. I will do this. I’ve been trying to not use so many layers of
queries but I guess I’ll have to accept the crosstab’s buggy nature.

Kay

Jerry Whittle said:
Crosstabs can be a little buggy with parameters. Your best bet is to create a
normal select query that includes the declared parameter and any other
criteria to gather up the records and fields that you want to see. Make sure
that this query works then save it with a name. Next create a crosstab query
using the first query as its record source.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


AccessKay said:
Good Morning,

I read the threads about creating parameters in crosstab queries and this
helped me to get it to work but the parameter box pops up when I try to save
the query. Though my changes are saved, I don’t think this is normal? It
happens in all views. Can anyone tell me what might be wrong? Here is my
SQL:

PARAMETERS [Enter Month and Year] DateTime;
TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
FROM Trans_Mstr_ODC
WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
Trans_Mstr_ODC.Product
PIVOT Trans_Mstr_ODC.ODC_Cost_Category;

TIA,
Kay
Click to expand...
Click to expand...
 
That is not the standard behavior. Simply saving the query should not display
the parameter prompt.

What version of Access are you using?

I would try copying the SQL text into a new blank query and see if you get the
same behavior. If not, then you have something in your malfunctioning query
that is causing the problem. The simplest cure would be to delete the
offending query and rename the new query with the name of the old query.

BACK UP YOUR DATABASE before doing this. Just in case.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
I did what you said about copying the SQL into a fresh query and this worked.
Strange…but my problem is solved (for now). BTW, I’m using Access 2007.

Thank you,
Kay


John Spencer said:
That is not the standard behavior. Simply saving the query should not display
the parameter prompt.

What version of Access are you using?

I would try copying the SQL text into a new blank query and see if you get the
same behavior. If not, then you have something in your malfunctioning query
that is causing the problem. The simplest cure would be to delete the
offending query and rename the new query with the name of the old query.

BACK UP YOUR DATABASE before doing this. Just in case.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Good Morning,

I read the threads about creating parameters in crosstab queries and this
helped me to get it to work but the parameter box pops up when I try to save
the query. Though my changes are saved, I don’t think this is normal? It
happens in all views. Can anyone tell me what might be wrong? Here is my
SQL:

PARAMETERS [Enter Month and Year] DateTime;
TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost
SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost]
FROM Trans_Mstr_ODC
WHERE (((Trans_Mstr_ODC.TransDate)=[Enter Month and Year]))
GROUP BY Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category,
Trans_Mstr_ODC.Product
PIVOT Trans_Mstr_ODC.ODC_Cost_Category;

TIA,
Kay
Click to expand...
.
Click to expand...
 
Back
Top