Why only 9 "Or" statements in a qry? How can I add more OR stateme

  • Thread starter Thread starter Iram
  • Start date Start date
I

Iram

I am using Access 2003. Why am I only limited to 9 "Or" statements and how
can I add more OR Statements to a query?
My situation...
I have a text box on a form that I am using for searches, when I click the
Ok button next to it, it opens a form ran by a query that has the below
parameter in a staggered approach on 9 records, however I need my query to
search by more than 9 table fields. How can I do this?
[Forms]![frmAllActive10DayInterviews]![SearchTextBox]

Thanks.
Iram
 
What does your table and data look like? I ask because the problem might be
with you table. Ideally you should have an entity in one field. For example
you can look up all the customers names 'Smith' in the Smith table LastName
field. If you have to look for similar data in various fields or tables, your
data isn't properly normalized.
 
I have more than 9 diferent table fields such as the below however I want to
create one master keyword query so that if I remember the sons first name but
not the parents (or vice versa) or pull all records that are interviewed by a
particular person, I want to be able to perform a master search. Does this
make sense?

Fathers First Name
Fathers Last Name
Mothers First Name
Mothers Last Name
Childs First Name
Childs Last Name
Interview Notes
Comments
Response
Interview By
....
....
....
....
...



Iram



Jerry Whittle said:
What does your table and data look like? I ask because the problem might be
with you table. Ideally you should have an entity in one field. For example
you can look up all the customers names 'Smith' in the Smith table LastName
field. If you have to look for similar data in various fields or tables, your
data isn't properly normalized.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Iram said:
I am using Access 2003. Why am I only limited to 9 "Or" statements and how
can I add more OR Statements to a query?
My situation...
I have a text box on a form that I am using for searches, when I click the
Ok button next to it, it opens a form ran by a query that has the below
parameter in a staggered approach on 9 records, however I need my query to
search by more than 9 table fields. How can I do this?
[Forms]![frmAllActive10DayInterviews]![SearchTextBox]

Thanks.
Iram
Click to expand...
Click to expand...
 
Iram said:
I am using Access 2003. Why am I only limited to 9 "Or" statements
and how can I add more OR Statements to a query?
My situation...
I have a text box on a form that I am using for searches, when I
click the Ok button next to it, it opens a form ran by a query that
has the below parameter in a staggered approach on 9 records, however
I need my query to search by more than 9 table fields. How can I do
this? [Forms]![frmAllActive10DayInterviews]![SearchTextBox]

Thanks.
Iram
Click to expand...

It is difficult to say without knowing the details of your table's
design, but it sounds as if you need to normalize your database design
so that the similar data that is currently stored in a single record in
mutliple fields gets stored in a single field in multiple rows.
 
You can have more than 9 criteria lines in query design view.

To add more criteria lines click in any criteria line and then select
Insert: Row from the menu.

If you select several rows and use the menu to insert row, you will
insert multiple new rows at one time.

That said, read Jerry Whittle's comment about a probable bad table design.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thanks for the response guys. It sounds like there is no way to exceed the 9
"Or" limit. I wanted to keep the database design simple in case I leave some
this company day. Right now I have one master table with about 30 fields. I
didn't want to change it to a relational db unless I really had to. In this
case I am going create teh master query to find the 9 most important fields
only.

Thanks anyway, you guys are awesome!

Iram/mcp


Bob Barrows said:
Iram said:
I am using Access 2003. Why am I only limited to 9 "Or" statements
and how can I add more OR Statements to a query?
My situation...
I have a text box on a form that I am using for searches, when I
click the Ok button next to it, it opens a form ran by a query that
has the below parameter in a staggered approach on 9 records, however
I need my query to search by more than 9 table fields. How can I do
this? [Forms]![frmAllActive10DayInterviews]![SearchTextBox]

Thanks.
Iram
Click to expand...

It is difficult to say without knowing the details of your table's
design, but it sounds as if you need to normalize your database design
so that the similar data that is currently stored in a single record in
mutliple fields gets stored in a single field in multiple rows.
Click to expand...
 
Whoops! I meant Customer table - not Smith table!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Jerry Whittle said:
What does your table and data look like? I ask because the problem might be
with you table. Ideally you should have an entity in one field. For example
you can look up all the customers names 'Smith' in the Smith table LastName
field. If you have to look for similar data in various fields or tables, your
data isn't properly normalized.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Iram said:
I am using Access 2003. Why am I only limited to 9 "Or" statements and how
can I add more OR Statements to a query?
My situation...
I have a text box on a form that I am using for searches, when I click the
Ok button next to it, it opens a form ran by a query that has the below
parameter in a staggered approach on 9 records, however I need my query to
search by more than 9 table fields. How can I do this?
[Forms]![frmAllActive10DayInterviews]![SearchTextBox]

Thanks.
Iram
Click to expand...
Click to expand...
 
I'm sorry to give the impression that there was a limit to the number of
Or expressions in a query. There isn't (see John's reply), but there is
a limit to the length of a query.

You might want to consider doing some concatenation and using LIKE with
wildcards:

In the top (Field) row:
Expr1: [FatherFirstName] & [FatherLastName] & ...

In the criterion row:
Like "*" & [Forms]![frmAllActive10DayInterviews]![SearchTextBox]
& "*"

Thanks for the response guys. It sounds like there is no way to
exceed the 9 "Or" limit. I wanted to keep the database design simple
in case I leave some this company day. Right now I have one master
table with about 30 fields. I didn't want to change it to a
relational db unless I really had to. In this case I am going create
teh master query to find the 9 most important fields only.

Thanks anyway, you guys are awesome!

Iram/mcp


Bob Barrows said:
Iram said:
I am using Access 2003. Why am I only limited to 9 "Or" statements
and how can I add more OR Statements to a query?
My situation...
I have a text box on a form that I am using for searches, when I
click the Ok button next to it, it opens a form ran by a query that
has the below parameter in a staggered approach on 9 records,
however I need my query to search by more than 9 table fields. How
can I do this? [Forms]![frmAllActive10DayInterviews]![SearchTextBox]

Thanks.
Iram
Click to expand...

It is difficult to say without knowing the details of your table's
design, but it sounds as if you need to normalize your database
design so that the similar data that is currently stored in a single
record in mutliple fields gets stored in a single field in multiple
rows.
Click to expand...
Click to expand...
 
I have more than 9 diferent table fields such as the below however
I want to create one master keyword query so that if I remember
the sons first name but not the parents (or vice versa) or pull
all records that are interviewed by a particular person, I want to
be able to perform a master search. Does this make sense?

Fathers First Name
Fathers Last Name
Mothers First Name
Mothers Last Name
Childs First Name
Childs Last Name
Interview Notes
Comments
Response
Interview By
...
...
...
...
..
Click to expand...

As Jerry Whittle quite correctly suggested, this is a data design
error. The entity you're modelling is PEOPLE, and they have a
FirstName and a LastName. You relate various people using a separate
table to define self joins, and then indicate in each self-join
record what kind of relationship it is.
 
Back
Top