Crosstab Query with Prompted Criteria

salemnj1

Board Regular
Joined
May 5, 2004
Messages
87
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...
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
What error message do you get?

What criteria are you trying to set?
 

salemnj1

Board Regular
Joined
May 5, 2004
Messages
87
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

Active Member
Joined
Nov 14, 2005
Messages
497
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

Board Regular
Joined
May 5, 2004
Messages
87
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! :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,383
Messages
5,571,818
Members
412,420
Latest member
Quintankerus
Top