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"
12 1681 ChipR 1,287
Recognized Expert Top Contributor
Can't tell what's going on in the pic. Can you post the text from the SQL view?
- 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 1,287
Recognized Expert Top Contributor
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.
The form is open when I run the query
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;
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
@rcollins
You could try your Where statement like this -
WHERE (((Query1.SurveyCode)=NZ([Forms]![OPCriteria]![Combo8],*)))
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
@rcollins - 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.
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: William Wisnieski |
last post by:
Hello Everyone,
I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the...
|
by: pelcovits |
last post by:
I am trying to set up an unbound form to enter report criteria.
I've followed the MS Office Assistance document: "Create a form to
enter report criteria" which describes how to enter data (such as
dates) in a text box. This procedure works fine. However, I also need
to enter data from a combo box and I cannot get this to work. On my
form...
|
by: JM |
last post by:
Hello,
I've created a Querydef in a Form_Load() sub. The form is a subform
that no longer has linked child fields. The form is bound to this
querydef. When I open the form, the fields are populated just fine.
However, when I add a new record using the new record control at the
bottom of the subform, it gets added with NULL for the two...
|
by: MLH |
last post by:
I have an A97 query (qryVehiclesNowners2) that has a table
field in it named . Depending on the selections made
in a number of criteria choices on a form, a field on the form
will have string values in it like...
Between #12/1/2004# And #12/31/2004# - or -
>= #6/10/2005# - or -
Between #6/1/2005# And #6/30/2005#
I want...
|
by: MLH |
last post by:
Am repeating question with different subject heading, perhaps
stating more clearly my problem...
I have an A97 query (qryVehiclesNowners2) that has a table
field in it named . Depending on the selections made
in a number of criteria choices on a form, a field on the form
will have string values in it like...
Between #12/1/2004# And...
| |
by: Steve Jorgensen |
last post by:
Mailing List management is a good example of a case where my conundrum arises.
Say there is a m-m relationship between parties and groups - anyone can be a
member of any combintation of groups. Now, let's say the user wants to be
able to send mailings to people who have various combinations of membership
and non-membership in those groups.
...
|
by: Mark Roughton |
last post by:
I have a form where the users need to view records for various
criteria, one of which is a date field on which they may wish to view
all related data for the selected date, for all dates upto and icluding
the selected date or all records on or after the selected date
The user selects either "=", >=" or "<=" from a combo box and then a
date...
|
by: DrJarmin |
last post by:
Hello
The problem is this: in the criteria for a list box I reference the
parent form - and Access KEEPS changing the criteria for one that
won't work. Details below:
I have a couple of list boxes on a form. In the row source I use the
primary key of the form as a criteria.
For example, I have company details on the form. The list box...
|
by: martin DH |
last post by:
Hello all,
I'm back with another SQL related problem. The details are below, but in short: I am using Access 2003. I have a table whose structure may include four different associate names per record (full structure below). I have a query that should return records that have one or more entered names (as criteria from a form) belonging to...
|
by: sharsy |
last post by:
Hello guys,
I would like some help in generating query criteria that will identify
credit cards that have expired on an access database.
The specific Field is formatted with a Data Type of
'Text' and has an input mask of '00/00', so all expiry dates are set
out for example as 10/13 (which represents October 2013).
I have hada brief go...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |