Thank you for your response. I think I understand how the
“ParaQuerySelect.mdb†sample works. I currently have a form that can bring
up a report with text boxes of “company nameâ€, “start dateâ€, “end date†and 4
combo boxes for age, salary and other criteria. That form seems to work look
well. The problem is that when I tried to increase the number of criteria to
11 with a couple more combo boxes, Access kind of RUNNING OUT OF REAL TIME
MEMORY. The following is the current parameter coding for the report:
SELECT employee.FileStartDate, RFixquestionset.Optional,
RFixquestionset.FixQuestionnumber, Company.CompanyID, Company.CompanyName,
RQ.Response, RQ.DetailedResponse, RQQuestion.QuestionNumber,
RQQuestion.QuestoinTextlookup, RQQuestion.QuestionID,
RFixquestionset.fixQuestionType, employee.Gender, employee.AgeGroup,
employee.[Salary Range], employee.EmployeeType, RQ.Abbreviationresponse,
IIf([Response]=1 Or [Abbreviationresponse]="Strongly Disagree",1,0) AS
Response1, IIf([Response]=2 Or [Abbreviationresponse]="Disagree",1,0) AS
Response2, IIf([Response]=3 Or [Abbreviationresponse]="Agree",1,0) AS
Response3, IIf([Response]=6 Or [Abbreviationresponse]="NA",1,0) AS Response5,
IIf([Response]=4 Or [Abbreviationresponse]="Strongly Agree",1,0) AS
Response4, IIf([Response]=4 Or [Abbreviationresponse]="Strongly
Agree","Strongly Agree",IIf([response]=3 Or
[Abbreviationresponse]="Agree","agree",IIf([Response]=2 Or
[Abbreviationresponse]="Disagree","Disagree",IIf([Response]=1 Or
[Abbreviationresponse]="strongly disagree","Strongly Disagree","NA")))) AS
Abbreviation
FROM RFixquestionset INNER JOIN ((Company INNER JOIN RQQuestion ON
Company.CompanyID = RQQuestion.CompanyID) INNER JOIN (employee INNER JOIN RQ
ON employee.EmployeeID = RQ.EmployeeID) ON RQQuestion.QuestionID =
RQ.QuestionID) ON RFixquestionset.FixQuestionnumber = RQQuestion.QuestionText
WHERE (((employee.FileStartDate) Between [forms]![update]![text1] And
[forms]![update]![Filestartdate2]) AND ((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6" Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2" Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5 ") AND
((employee.Gender)=[forms]![update]![text12]) AND
((employee.AgeGroup)=[forms]![update]![text31]) AND ((employee.[Salary
Range])=[forms]![update]![text21]) AND
((employee.EmployeeType)=[forms]![update]![text23])) OR
(((employee.FileStartDate) Between [forms]![update]![text1] And
[forms]![update]![Filestartdate2]) AND ((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6" Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2" Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5") AND
((employee.AgeGroup)=[forms]![update]![text31]) AND ((employee.[Salary
Range])=[forms]![update]![text21]) AND
((employee.EmployeeType)=[forms]![update]![text23]) AND
(([forms]![update]![text12]) Is Null)) OR (((employee.FileStartDate) Between
[forms]![update]![text1] And [forms]![update]![Filestartdate2]) AND
((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6" Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2" Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5") AND
((employee.Gender)=[forms]![update]![text12]) AND ((employee.[Salary
Range])=[forms]![update]![text21]) AND
((employee.EmployeeType)=[forms]![update]![text23]) AND
(([forms]![update]![text31]) Is Null)) OR (((employee.FileStartDate) Between
[forms]![update]![text1] And [forms]![update]![Filestartdate2]) AND
((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6" Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2" Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5") AND ((employee.[Salary
Range])=[forms]![update]![text21]) AND
((employee.EmployeeType)=[forms]![update]![text23]) AND
(([forms]![update]![text12]) Is Null) AND (([forms]![update]![text31]) Is
Null)) OR (((employee.FileStartDate) Between [forms]![update]![text1] And
[forms]![update]![Filestartdate2]) AND ((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6" Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2" Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5†AND
((employee.Gender)=[forms]![update]![text12]) AND
((employee.AgeGroup)=[forms]![update]![text31]) AND
((employee.EmployeeType)=[forms]![update]![text23]) AND
(([forms]![update]![text21]) Is Null)) OR (((employee.FileStartDate) Between
[forms]![update]![text1] And [forms]![update]![Filestartdate2]) AND
((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6 - Compensation" Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2" Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5") AND
((employee.AgeGroup)=[forms]![update]![text31]) AND
((employee.EmployeeType)=[forms]![update]![text23]) AND
(([forms]![update]![text12]) Is Null) AND (([forms]![update]![text21]) Is
Null)) OR (((employee.FileStartDate) Between [forms]![update]![text1] And
[forms]![update]![Filestartdate2]) AND ((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6" Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2 " Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5") AND
((employee.Gender)=[forms]![update]![text12]) AND
((employee.EmployeeType)=[forms]![update]![text23]) AND
(([forms]![update]![text31]) Is Null) AND (([forms]![update]![text21]) Is
Null)) OR (((employee.FileStartDate) Between [forms]![update]![text1] And
[forms]![update]![Filestartdate2]) AND ((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6" Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2" Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5") AND
((employee.EmployeeType)=[forms]![update]![text23]) AND
(([forms]![update]![text12]) Is Null) AND (([forms]![update]![text31]) Is
Null) AND (([forms]![update]![text21]) Is Null)) OR
(((employee.FileStartDate) Between [forms]![update]![text1] And
[forms]![update]![Filestartdate2]) AND ((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6" Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2" Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5") AND
((employee.Gender)=[forms]![update]![text12]) AND
((employee.AgeGroup)=[forms]![update]![text31]) AND ((employee.[Salary
Range])=[forms]![update]![text21]) AND (([forms]![update]![text23]) Is Null))
OR (((employee.FileStartDate) Between [forms]![update]![text1] And
[forms]![update]![Filestartdate2]) AND ((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6 " Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2" Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5") AND
((employee.AgeGroup)=[forms]![update]![text31]) AND ((employee.[Salary
Range])=[forms]![update]![text21]) AND (([forms]![update]![text12]) Is Null)
AND (([forms]![update]![text23]) Is Null)) OR (((employee.FileStartDate)
Between [forms]![update]![text1] And [forms]![update]![Filestartdate2]) AND
((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6" Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2" Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5") AND
((employee.Gender)=[forms]![update]![text12]) AND ((employee.[Salary
Range])=[forms]![update]![text21]) AND (([forms]![update]![text31]) Is Null)
AND (([forms]![update]![text23]) Is Null)) OR (((employee.FileStartDate)
Between [forms]![update]![text1] And [forms]![update]![Filestartdate2]) AND
((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6" Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2" Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5") AND ((employee.[Salary
Range])=[forms]![update]![text21]) AND (([forms]![update]![text12]) Is Null)
AND (([forms]![update]![text31]) Is Null) AND (([forms]![update]![text23]) Is
Null)) OR (((employee.FileStartDate) Between [forms]![update]![text1] And
[forms]![update]![Filestartdate2]) AND ((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6" Or
(RFixquestionset.fixQuestionType)="Section 1" Or
(RFixquestionset.fixQuestionType)="Section 2" Or
(RFixquestionset.fixQuestionType)="Section 3" Or
(RFixquestionset.fixQuestionType)="Section 4" Or
(RFixquestionset.fixQuestionType)="Section 5") AND
((employee.Gender)=[forms]![update]![text12]) AND
((employee.AgeGroup)=[forms]![update]![text31]) AND
(([forms]![update]![text21]) Is Null) AND (([forms]![update]![text23]) Is
Null)) OR (((employee.FileStartDate) Between [forms]![update]![text1] And
[forms]![update]![Filestartdate2]) AND ((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6 - Compensation" Or
(RFixquestionset.fixQuestionType)="Section 1 - Company, Culture, and Value"
Or (RFixquestionset.fixQuestionType)="Section 2 - Management" Or
(RFixquestionset.fixQuestionType)="Section 3 - Position" Or
(RFixquestionset.fixQuestionType)="Section 4 - Recognition and Growth" Or
(RFixquestionset.fixQuestionType)="Section 5 - Working Condition") AND
((employee.AgeGroup)=[forms]![update]![text31]) AND
(([forms]![update]![text12]) Is Null) AND (([forms]![update]![text21]) Is
Null) AND (([forms]![update]![text23]) Is Null)) OR
(((employee.FileStartDate) Between [forms]![update]![text1] And
[forms]![update]![Filestartdate2]) AND ((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6 - Compensation" Or
(RFixquestionset.fixQuestionType)="Section 1 - Company, Culture, and Value"
Or (RFixquestionset.fixQuestionType)="Section 2 - Management" Or
(RFixquestionset.fixQuestionType)="Section 3 - Position" Or
(RFixquestionset.fixQuestionType)="Section 4 - Recognition and Growth" Or
(RFixquestionset.fixQuestionType)="Section 5 - Working Condition") AND
((employee.Gender)=[forms]![update]![text12]) AND
(([forms]![update]![text31]) Is Null) AND (([forms]![update]![text21]) Is
Null) AND (([forms]![update]![text23]) Is Null)) OR
(((employee.FileStartDate) Between [forms]![update]![text1] And
[forms]![update]![Filestartdate2]) AND ((RFixquestionset.Optional)=0) AND
((Company.CompanyName)=[forms]![update]![text2]) AND
((RFixquestionset.fixQuestionType)="Section 6 - Compensation" Or
(RFixquestionset.fixQuestionType)="Section 1 - Company, Culture, and Value"
Or (RFixquestionset.fixQuestionType)="Section 2 - Management" Or
(RFixquestionset.fixQuestionType)="Section 3 - Position" Or
(RFixquestionset.fixQuestionType)="Section 4 - Recognition and Growth" Or
(RFixquestionset.fixQuestionType)="Section 5 - Working Condition") AND
(([forms]![update]![text12]) Is Null) AND (([forms]![update]![text31]) Is
Null) AND (([forms]![update]![text21]) Is Null) AND
(([forms]![update]![text23]) Is Null));
Is there anything wrong with my code? Thanks in advance.
Roger Carlson said:
All right, then. Try this sample: "ParaQuerySelect.mdb". It shows how to
create a form with two combos that act as parameters for the query.
--
--Roger Carlson
Access Database Samples:
www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
"questionnaire database analyst"
Thanks. However, that seems to be very complicated for users... is there any
alternative way that would work by having users inputting information into
the textboxes or combox box in a form?
btw, does anybody has any idea how come the way I did it doesn't work? I
tried earlier with 4 criteria and it worked... but with 11... it doesn't...
any idea?
Roger Carlson said:
On my website (
www.rogersaccesslibrary.com), are a couple of small Access
sample databases which might give you a start. The all start with
"CreateQueries". CreateQueries2.mdb has a lot of simple query creation
forms, but CreateQueries5.mdb is the most flexible.
--
--Roger Carlson
Access Database Samples:
www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
"questionnaire database analyst"
I have a questionnaire database that contains participant's demographic
information such as age, salary range, employee type, age group,
departments,
location, and managers. We want to be able to run reports that can show
participant's responses based the combination of the above demographic
information chosen by user. For instance, if I type in 19 and below in
the
"Age" field in the form and 9000 and less in the "Salary Range" field in
the
form, only the participants that meet these specific criteria are included
in
the report. On the other side, if nothing were entered in the form, the
report would include all of the participants' responses.
I have tried to run a multiple parameter by entering
[forms]![formname]![age] or [forms]![formname]![age] Is Null in the query
for
age, [forms]![formname]![salary range] or [forms]![formname]![salary
range]
Is Null for salary range, and so on for the other demographic criteria in
the
Criteria/Or section.
It worked well until I have 11 (which is all of my need for criteria) of
the
same kind of criteria entered [forms]![formname]![fieldname] or
[forms]![formname]![fieldname] Is Null. I CAN'T open up the query in
design
form anymore!!!!!!!!!!
Is there any ways that I can get around this problem? Am I doing it the
right way? Is that any samples I can look at? Thank you for advance.
