By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Too few parameters. Expected 1. When selecting CrossTab Query as Subform SourceObject

100+
P: 176
Please help me with this.

I have a form in which I would like to present results from a CrossTab query in a subform. I don't need the query to be bounded to a master field. Also, I have a parameter in the query as one of the main form controls. Please look at the query:

PARAMETERS [Forms]![Customers_Personal]![Id] Long;
TRANSFORM Sum(LoansSumAmountAndYearAndCity.Amount) AS SumOfAmount
SELECT LoansSumAmountAndYearAndCity.Customers.Id, Sum(LoansSumAmountAndYearAndCity.Amount) AS [Total Of Amount]
FROM LoansSumAmountAndYearAndCity
WHERE (((LoansSumAmountAndYearAndCity.Customers.Id)=[Forms]![Customers_Personal]![Id]))
GROUP BY LoansSumAmountAndYearAndCity.Customers.Id
PIVOT LoansSumAmountAndYearAndCity.Year;

When I select the query as the Source Object for the subform I get the following error - Too few parameters. Expected 1.

Does anyone knows what is the reason for this error or had encountered into something similiar, and can help?

Thanks!
Dec 6 '06 #1
Share this Question
Share on Google+
6 Replies


nico5038
Expert 2.5K+
P: 3,072
I'm puzzled by the field: LoansSumAmountAndYearAndCity.Customers.Id
Are you sure you have a period between Customers and ID ?

Nic;o)
Dec 6 '06 #2

NeoPa
Expert Mod 15k+
P: 31,616
PARAMETERS Declaration
Declares the name and data type of each parameter in a parameter query.

Syntax
PARAMETERS name datatype [, name datatype [, ...]]

The PARAMETERS declaration has these parts:

Part Description
name The name of the parameter. Assigned to the Name property of the Parameter object and used to identify this parameter in the Parameters collection. You can use name as a string that is displayed in a dialog box while your application runs the query. Use brackets ([ ]) to enclose text that contains spaces or punctuation. For example, [Low price] and [Begin report with which month?] are valid name arguments.
datatype One of the primary Microsoft Jet SQL data types or their synonyms.
I found how to set up the parameter from looking in Help, but I wasn't able to find out how parameters are passsed from the code (rather than allowing/expecting the operator to provide a value).
If anyone can throw any light on this I think it would be helpful.
Dec 7 '06 #3

100+
P: 176
I'm puzzled by the field: LoansSumAmountAndYearAndCity.Customers.Id
Are you sure you have a period between Customers and ID ?

Nic;o)
Yes, CrossTab query creates those kind of names.
Dec 7 '06 #4

NeoPa
Expert Mod 15k+
P: 31,616
Michael,

I don't know if you noticed, but the Help excerpt implies you've got your Parameters clause wrong.
I too would like to know how it should be done.
Dec 7 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Yes, CrossTab query creates those kind of names.
This doesn't sound right Michael.

Create the crosstab query again without the parameters and see what result you get.

Mary
Dec 7 '06 #6

nico5038
Expert 2.5K+
P: 3,072
I often use a different approach for crosstable queries where I need to filter.
I just create first the filter query and that's then "cleanly" used for the crossatble query.
For very large tables I even revert to using a maketable or append query to get first the filtered recordset before applying a crosstable query.

Nic;o)
Dec 7 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.