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

Criteria Form

rcollins
100+
P: 234
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"
Feb 3 '09 #1
Share this Question
Share on Google+
12 Replies


Expert 100+
P: 1,287
Can't tell what's going on in the pic. Can you post the text from the SQL view?
Feb 3 '09 #2

rcollins
100+
P: 234
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(Query1.ReturnedBadAddress) AS CountOfReturnedBadAddress
  2. SELECT Query1.CountyofSurvey, Query1.Question, Query1.SurveyCode, Count(Query1.ReturnedBadAddress) AS [Total Of ReturnedBadAddress], Count(Query1.Answer) AS Total
  3. FROM Query1
  4. GROUP BY Query1.CountyofSurvey, Query1.Question, Query1.SurveyCode
  5. PIVOT Query1.Answer;
  6.  
This sql does not have the criteria listed in it
Here it is with it and I get the error.

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(Query1.ReturnedBadAddress) AS CountOfReturnedBadAddress
  2. SELECT Query1.CountyofSurvey, Query1.Question, Query1.SurveyCode, Count(Query1.ReturnedBadAddress) AS [Total Of ReturnedBadAddress], Count(Query1.Answer) AS Total
  3. FROM Query1
  4. WHERE (((Query1.SurveyCode)=[Forms]![OPCriteria]![Combo8]))
  5. GROUP BY Query1.CountyofSurvey, Query1.Question, Query1.SurveyCode
  6. PIVOT Query1.Answer;
  7.  
My form is named OPCriteria and the fileds are Combo6 for Quarter and Combo8 for Location.
Feb 3 '09 #3

Expert 100+
P: 1,287
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.
Feb 3 '09 #4

rcollins
100+
P: 234
The form is open when I run the query
Feb 3 '09 #5

DonRayner
Expert 100+
P: 489
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.

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![OPCriteria]![Combo8] Text ( 255 );
  2. TRANSFORM Count(Query1.ReturnedBadAddress) AS CountOfReturnedBadAddress
  3. SELECT Query1.CountyofSurvey, Query1.Question, Query1.SurveyCode, Count(Query1.ReturnedBadAddress) AS [Total Of ReturnedBadAddress], Count(Query1.Answer) AS Total
  4. FROM Query1
  5. WHERE (((Query1.SurveyCode)=[Forms]![OPCriteria]![Combo8]))
  6. GROUP BY Query1.CountyofSurvey, Query1.Question, Query1.SurveyCode
  7. PIVOT Query1.Answer;
Feb 4 '09 #6

rcollins
100+
P: 234
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
Feb 4 '09 #7

DonRayner
Expert 100+
P: 489
@rcollins
You could try your Where statement like this
Expand|Select|Wrap|Line Numbers
  1. WHERE (((Query1.SurveyCode)=NZ([Forms]![OPCriteria]![Combo8],*))) 
Feb 5 '09 #8

rcollins
100+
P: 234
Here is my Where

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

and I get this error
Syntax error (missing operator) in query expression
Feb 5 '09 #9

DonRayner
Expert 100+
P: 489
@rcollins
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 5 '09 #10

rcollins
100+
P: 234
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
Feb 5 '09 #11

rcollins
100+
P: 234
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
Feb 5 '09 #12

DonRayner
Expert 100+
P: 489
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_dy...stab_field.htm
http://support.microsoft.com/kb/328320
Feb 6 '09 #13

Post your reply

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