Cross tab query

[123]

Thank you for your help….

Why in Cross tab query I cant using in Criteria this expression

[Forms]![formname]![controlname]

This message box appear

The Microsoft Jet database engine does not recognize

[Forms]![formname]!controlname] as a valid field name or expression

how to solve this problem

Thank you

 

[Andrew Smith]

You need to add it to the parameters collection.

Open the query in design view. Select "Parameters" in the "Query" menu.
Enter the parameter name ([Forms]![formname]![controlname]) in the parameter
field, and select the correct data type in the data type field.

Don't ask me why you have to do this with crosstab queries, and not simple
select queries, you just do!

 

[123]

MR. Andrew Smith
Thank you for your reply and help...
I'm using Query -->Parameters and put my Parameters like this
[Forms]![formname]![controlname] put I don't know why not work. When run
query (view data sheet ) display msgbox (enter Parameter value) ok when put
my parameter display all record I don't know the reason is this bug in
access..
any help thank you..

 

[Duane Hookom]

Post your SQL view to the news group so we can see what you are attempting
to do.

 

[123]

Thank YOU:

PARAMETERS [Forms]![frmreport]![cbogroupnumber] Text ( 255 );
TRANSFORM Count(TblCompany.ID) AS CountOfID
SELECT TblCompany.GroupNumber, TblCompany.NameOfCompany,
Count(TblCompany.ID) AS CountOfID1
FROM TblCompany
GROUP BY TblCompany.GroupNumber, TblCompany.NameOfCompany
PIVOT TblCompany.job;

 

[Duane Hookom]

Where did you put the criteria? Is the form open? Are you getting an error
or a prompt?

 

[123]

Thank you for your help:

Where you put criteria

When test result I click run query and put the criteria when input box
Parameters appear and click ok (for testing only) and if this work I put it
in form and subform

Is the form open:

as I tell YOU in previous question I test the query first by using Query
parameters input box then using form and subform and form and subform (will
be opened) if the test is done right.

Are you getting an error

I don't receive any error message..

Thank YOU

Where did you put the criteria? Is the form open? Are you getting an error
or a prompt?

--
Duane Hookom
MS Access MVP

Thank YOU:

PARAMETERS [Forms]![frmreport]![cbogroupnumber] Text ( 255 );
TRANSFORM Count(TblCompany.ID) AS CountOfID
SELECT TblCompany.GroupNumber, TblCompany.NameOfCompany,
Count(TblCompany.ID) AS CountOfID1
FROM TblCompany
GROUP BY TblCompany.GroupNumber, TblCompany.NameOfCompany
PIVOT TblCompany.job;

 

[Andrew Smith]

The SQL you posted has no WHERE clause - this means that it will select all
records, ie you have not entered a criteria for your query. You can either
type the WHERE clause into the SQL (after the FROM clause and before the
GROUP BY clause), or you can enter it as a new column in the query design
grid - ie add the field you want to filter on and type
[Forms]![frmreport]![cbogroupnumber] in the criteria box, and choose "Where"
from the totals drop down box.

If you want to type in the WHERE clause the SQL would become:

PARAMETERS [Forms]![frmreport]![cbogroupnumber] Text ( 255 );
TRANSFORM Count(TblCompany.ID) AS CountOfID
SELECT TblCompany.GroupNumber, TblCompany.NameOfCompany,
Count(TblCompany.ID) AS CountOfID1
FROM TblCompany
WHERE NameOfField = [Forms]![frmreport]![cbogroupnumber]
GROUP BY TblCompany.GroupNumber, TblCompany.NameOfCompany
PIVOT TblCompany.job;

 

[123]

Thank you for your help and answer:
This msgbox display after add where condition:
This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

 

[Andrew Smith]

You get this message if the data type specified in "parameters" does not
match the data type actually entered. Please check the type of data that
your field is set to, and that this is the same data type you have specified
in the parameters - this was text in your previous post. Are you running the
query on a text field? If not, change the data type in the the parameters
dialog box.

There may be other causes of this error that I have not come across. Please
post back your new SQL, and the data type of the fields in your table if it
still does not work.