Crosstab Query with Prompted Criteria

[salemnj1]

Is there a way to add a prompted criteria to a cross tab query? I tried to type [Enter Date] in the criteria line of a cross tab query and I get an error message...

 

[nullZero]

Can you post your SQL?

 

[Norie]

What error message do you get?

What criteria are you trying to set?

 

[salemnj1]

This works:

SELECT "BC" AS Tag, "TRX" AS Metric, [payer-potential-bc-temptable].MONTH_DK, [payer-potential-bc2].MMK_BRAND_GRP_L, "B- " & [payer-potential-bc2].[GEO_BC_L] AS Location, [payer-potential-bc2].MMK_PLAN_GRP_DK, [payer-potential-bc2].MMK_PLAN_GRP_TYPE_DK, [payer-potential-bc2].MMK_DISPLAY, [payer-potential-bc2].MMK_CALCULATE, Sum([payer-potential-bc-temptable].MMK_TRX_COUNT_MKT) AS MARKET_BRAND, Sum([payer-potential-bc-temptable].MMK_TRX_COUNT_MKT_TOT) AS MARKET_TOTAL, [MARKET_BRAND]/[MARKET_TOTAL] AS [% of Market], Sum([payer-potential-bc2].[200505]) AS SumOf200505, Sum([payer-potential-bc2].[200506]) AS SumOf200506, Sum([payer-potential-bc2].[200507]) AS SumOf200507, Sum([payer-potential-bc2].[200508]) AS SumOf200508, Sum([payer-potential-bc2].[200509]) AS SumOf200509, Sum([payer-potential-bc2].[200510]) AS SumOf200510
FROM [payer-potential-bc2] INNER JOIN [payer-potential-bc-temptable] ON ([payer-potential-bc2].MMK_PLAN_GRP_DK = [payer-potential-bc-temptable].MMK_PLAN_GRP_DK) AND ([payer-potential-bc2].GEO_BC_L = [payer-potential-bc-temptable].GEO_BC_L)
GROUP BY "BC", "TRX", [payer-potential-bc-temptable].MONTH_DK, [payer-potential-bc2].MMK_BRAND_GRP_L, "B- " & [payer-potential-bc2].[GEO_BC_L], [payer-potential-bc2].MMK_PLAN_GRP_DK, [payer-potential-bc2].MMK_PLAN_GRP_TYPE_DK, [payer-potential-bc2].MMK_DISPLAY, [payer-potential-bc2].MMK_CALCULATE
HAVING ((([payer-potential-bc-temptable].MONTH_DK)=200510))
ORDER BY Sum([payer-potential-bc-temptable].MMK_TRX_COUNT_MKT) DESC;

But this doesn't:

SELECT "BC" AS Tag, "TRX" AS Metric, [payer-potential-bc-temptable].MONTH_DK, [payer-potential-bc2].MMK_BRAND_GRP_L, "B- " & [payer-potential-bc2].[GEO_BC_L] AS Location, [payer-potential-bc2].MMK_PLAN_GRP_DK, [payer-potential-bc2].MMK_PLAN_GRP_TYPE_DK, [payer-potential-bc2].MMK_DISPLAY, [payer-potential-bc2].MMK_CALCULATE, Sum([payer-potential-bc-temptable].MMK_TRX_COUNT_MKT) AS MARKET_BRAND, Sum([payer-potential-bc-temptable].MMK_TRX_COUNT_MKT_TOT) AS MARKET_TOTAL, [MARKET_BRAND]/[MARKET_TOTAL] AS [% of Market], Sum([payer-potential-bc2].[200505]) AS SumOf200505, Sum([payer-potential-bc2].[200506]) AS SumOf200506, Sum([payer-potential-bc2].[200507]) AS SumOf200507, Sum([payer-potential-bc2].[200508]) AS SumOf200508, Sum([payer-potential-bc2].[200509]) AS SumOf200509, Sum([payer-potential-bc2].[200510]) AS SumOf200510
FROM [payer-potential-bc2] INNER JOIN [payer-potential-bc-temptable] ON ([payer-potential-bc2].MMK_PLAN_GRP_DK = [payer-potential-bc-temptable].MMK_PLAN_GRP_DK) AND ([payer-potential-bc2].GEO_BC_L = [payer-potential-bc-temptable].GEO_BC_L)
GROUP BY "BC", "TRX", [payer-potential-bc-temptable].MONTH_DK, [payer-potential-bc2].MMK_BRAND_GRP_L, "B- " & [payer-potential-bc2].[GEO_BC_L], [payer-potential-bc2].MMK_PLAN_GRP_DK, [payer-potential-bc2].MMK_PLAN_GRP_TYPE_DK, [payer-potential-bc2].MMK_DISPLAY, [payer-potential-bc2].MMK_CALCULATE
HAVING ((([payer-potential-bc-temptable].MONTH_DK)=[enter month dk]))
ORDER BY Sum([payer-potential-bc-temptable].MMK_TRX_COUNT_MKT) DESC;

The error message I get reads "The Microsoft jet database engine does not recognize '[enter month dk]' as a valid field name or expression.

Not sure why in a cross tab query you can't put criteria in brackets to prompt the user for the value.. I want this so I can set up a query non-access users can run without knowing how to edit a query in design view.

 

[nullZero]

This is from the MS Access Help, I tried it on a mock up of your table using your SQL and it worked. See where I've bolded thier instructions, I only did the first part,, not the second part (in italics), I don't think you need the second part because your parameter is on your table and not your crosstab.

The Microsoft Jet database engine does not recognize <name> as a valid field name or expression. (Error 3070)
The specified name is not a recognized field name or a valid expression. In a query, this error can occur if you enter a name that improperly refers to a database, table, or field.

Possible causes with Microsoft® Access:

You have a parameter in a crosstab query or in a query that a crosstab query or chart is based on, and the parameter data type is not explicitly specified in the Query Parameters dialog box. To solve the problem:
In the query that contains the parameter, specify the parameter and its data type in the Query Parameters dialog box. And;
Set the ColumnHeadings property for the query that contains the parameter. In any type of query, you have improperly referred to a database, table, or field. For example, this error can occur if you refer to a field named Salary in an expression, but you misspell the field name, such as [Sallary]*1.1.

 

[salemnj1]

thanks

after setting the parameter value it works now... after years of using MS Acess I never realized you sometimes have to do that! Thanks!