Criteria Form

[rcollins]

I have a form with 2 combo boxes on them. I want to pick info and run the report. the error I get when I put criteria in the query is "The Microsoft Jet Database does not recognize "[Forms]![OPCriteria]![Combo8]" as a valid field name or expression". I tried to do just [county] and have it prompt me but I still get the same error only that "The Microsoft Jet Database does not recognize "[county]" as a valid field name or expression"

[ChipR]

Can't tell what's going on in the pic. Can you post the text from the SQL view?

[rcollins]

TRANSFORM Count(Query1.ReturnedBadAddress) AS CountOfReturnedBadAddress
SELECT Query1.CountyofSurvey, Query1.Question, Query1.SurveyCode, Count(Query1.ReturnedBadAddress) AS [Total Of ReturnedBadAddress], Count(Query1.Answer) AS Total
FROM Query1
GROUP BY Query1.CountyofSurvey, Query1.Question, Query1.SurveyCode
PIVOT Query1.Answer;

This sql does not have the criteria listed in it
Here it is with it and I get the error.

TRANSFORM Count(Query1.ReturnedBadAddress) AS CountOfReturnedBadAddress
SELECT Query1.CountyofSurvey, Query1.Question, Query1.SurveyCode, Count(Query1.ReturnedBadAddress) AS [Total Of ReturnedBadAddress], Count(Query1.Answer) AS Total
FROM Query1
WHERE (((Query1.SurveyCode)=[Forms]![OPCriteria]![Combo8]))
GROUP BY Query1.CountyofSurvey, Query1.Question, Query1.SurveyCode
PIVOT Query1.Answer;

My form is named OPCriteria and the fileds are Combo6 for Quarter and Combo8 for Location.

[ChipR]

Maybe someone experienced with crosstab queries can provide some insight. The only thing I can see is that the form with the combo on it needs to be open to run the query, but I think that would cause a different error message if it were not.

[rcollins]

The form is open when I run the query

[DonRayner]

Try setting the query parameter type for any form referances in your Where statement. I set it to Text (255), you will have to set it to whatever your data type is.

PARAMETERS [Forms]![OPCriteria]![Combo8] Text ( 255 );
TRANSFORM Count(Query1.ReturnedBadAddress) AS CountOfReturnedBadAddress
SELECT Query1.CountyofSurvey, Query1.Question, Query1.SurveyCode, Count(Query1.ReturnedBadAddress) AS [Total Of ReturnedBadAddress], Count(Query1.Answer) AS Total
FROM Query1
WHERE (((Query1.SurveyCode)=[Forms]![OPCriteria]![Combo8]))
GROUP BY Query1.CountyofSurvey, Query1.Question, Query1.SurveyCode
PIVOT Query1.Answer;

[rcollins]

OK. Almost there. I put in the parameters for Combo6 and Combo8. Two issues.
1. How can I set OPCriteria form so if the form has null value it prints all?
2. On the questions, we have choices 0-5. When I run the query, if , for example, Craig has noone that answered 1 for any question. When I pick Craig from the dropdown on the Criteria form it errors because there isn't a 1 at all. At that point the box that shws count of 1 has No such field in the field list error.
Thanks

[DonRayner]

OK. Almost there. I put in the perameters for Combo6 and Combo8. Two issues.
1. How can I set OPCriteria form so if the form has null value it prints all?
2. On the questions, we have choices 0-5. When I run the query, if , for example, Craig has noone that answered 1 for any question. When I pick Craig from the dropdown on the Criteria form it errors because there isn't a 1 at all. At that point the box that shws count of 1 has No such field in the field list error.
Thanks

You could try your Where statement like this

WHERE (((Query1.SurveyCode)=NZ([Forms]![OPCriteria]![Combo8],*)))

[rcollins]

Here is my Where

WHERE (((Query1.Surve yCode)=NZ[Forms]![OPCriteria]![Combo6],*) AND ((Query1.County ofSurvey)=NZ[Forms]![OPCriteria]![Combo8],*))

and I get this error
Syntax error (missing operator) in query expression

[DonRayner]

Here is my Where

WHERE (((Query1.Surve yCode)=NZ[Forms]![OPCriteria]![Combo6],*) AND ((Query1.County ofSurvey)=NZ[Forms]![OPCriteria]![Combo8],*))

and I get this error
Syntax error (missing operator) in query expression

WHERE (((Query1.SurveyCode)=NZ([Forms]![OPCriteria]![Combo6],*)) AND ((Query1.CountyofSurvey)=(NZ[Forms]![OPCriteria]![Combo8],*)))

Do you see the difference? NZ() is a function with a syntax like this.
NZ(Item to check, Value If 0). You didn't include the brackets in your statement.

[rcollins]

So I am really not sure we have this right. Let me try to re explain. My query runs good. I get all of the values I need. Example of 2 counties:
Aspen
Answer 0=count of 1
Answer 1=count of 4
Answer 2=count of 6
Answer 3=cpunt of 2
Answer 4=count of 6
Answer 5=count of 5
Craig
Answer 0=count of 1
Answer 2=count of 6
Answer 3=cpunt of 2
Answer 4=count of 6
Answer 5=count of 5
Notice that nobody in the county of craig answered "1" to any of the questions. there are text boxes on my report for 0-5
since there is no count for "1" from Craig, if I run just craig by itself, it cannot find the field value 1 from the query and wont run. When I look at the report in design wiew, with filtered for Craig, the filed choice dfor craig is no longer there and the little green triangle is in the upper left hand corner. If I remove the filter, than it is ok

[rcollins]

so if my feild list does not show one of the fields, can I make it not show at all? I think this is a report issue now not a query issue

[DonRayner]

Here are a couple of links to generating dynamic crostab reports. And I'm sure if you did a search on "crosstab reports" on Bytes you would find lots of information as well.

http://www.blueclaw-db.com/report_dynamic_crosstab_field.htm

http://support.microsoft.com/kb/328320