Multiple "AND" in where clause

[kay]

I have a table with 2+9 fields.
I want to display rows for if [field3] and [field4] and [field5] and
[field6] and [field7] and [field8] is not blank.
I am trying this using Where Clause but it's not working.
I tried
Where (field3 is not null) and (field4 is not null) and (field5 is not null)
and so and so...
Please help.
Thanks

 

[Jeff Boyce]

If your table has repeating fields (and your description implies that it
does), then you have a spreadsheet, not a relational database table. Access
is optimized for well-normalized data, not 'sheet data.

You can pay now (normalize your data structure) or pay later (modifying your
table structure, queries, forms, reports, etc. every time you need to
add/remove another field).

If you decide you want to spend a lot of time and effort maintaining this
over the long term (i.e., pay later), take a look in Access HELP for the
Nz() function.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[KARL DEWEY]

I have a table with 2+9 fields.
What does '2+9 fields' mean?

A lot of folks mix logical AND with the english language 'and'.
Your use of 'AND' says that all of the fields must not be null or saying it
another way that no field can be null.

Maybe you want to use 'OR' instead.

 

[kay]

2+9 means - I have values in all rows for first two fields. but for rest 9
fileds (3rd field to 11th field) some rows for all these 9 fields are blank
and some are non blank rows so What I want to do is that display rows where
3rd fields to 11th fields are non blank (in English 3rd field or 4th or
5th.... is non blank)
i hope i am able to explain my point.

What does '2+9 fields' mean?

A lot of folks mix logical AND with the english language 'and'.
Your use of 'AND' says that all of the fields must not be null or saying it
another way that no field can be null.

Maybe you want to use 'OR' instead.

--
Build a little, test a little.

I have a table with 2+9 fields.
I want to display rows for if [field3] and [field4] and [field5] and
[field6] and [field7] and [field8] is not blank.
I am trying this using Where Clause but it's not working.
I tried
Where (field3 is not null) and (field4 is not null) and (field5 is not null)
and so and so...
Please help.
Thanks

 

[John W. Vinson]

I have a table with 2+9 fields.
I want to display rows for if [field3] and [field4] and [field5] and
[field6] and [field7] and [field8] is not blank.
I am trying this using Where Clause but it's not working.
I tried
Where (field3 is not null) and (field4 is not null) and (field5 is not null)
and so and so...
Please help.
Thanks

Your WHERE clause will return only those records where *NONE* of the fields
are NULL.

AND seems to be an english language conjunction, but it isn't - it's an
operator in Boolean algebra. The expression A AND B is TRUE if both A and B
are true; it's FALSE if either one of A or B is false. So if Field4 is NULL,
the expression

(field3 is not null) and (field4 is not null)

will be FALSE, whatever other values you might have.

As suggested you can change all your "and" operators to "or" - the OR operator
will return TRUE if A is true, or B is true, or both are true.

 

[KARL DEWEY]

Did you try it using OR instead of AND?

--
Build a little, test a little.

2+9 means - I have values in all rows for first two fields. but for rest 9
fileds (3rd field to 11th field) some rows for all these 9 fields are blank
and some are non blank rows so What I want to do is that display rows where
3rd fields to 11th fields are non blank (in English 3rd field or 4th or
5th.... is non blank)
i hope i am able to explain my point.

I have a table with 2+9 fields.

What does '2+9 fields' mean?

A lot of folks mix logical AND with the english language 'and'.
Your use of 'AND' says that all of the fields must not be null or saying it
another way that no field can be null.

Maybe you want to use 'OR' instead.

--
Build a little, test a little.

I have a table with 2+9 fields.
I want to display rows for if [field3] and [field4] and [field5] and
[field6] and [field7] and [field8] is not blank.
I am trying this using Where Clause but it's not working.
I tried
Where (field3 is not null) and (field4 is not null) and (field5 is not null)
and so and so...
Please help.
Thanks

 

[Keven Denen]

Did you try it using OR instead of AND?

--
Build a little, test a little.

2+9 means - I have values in all rows for first two fields. but for rest 9
fileds (3rd field to 11th field) some rows for all these 9 fields are blank
and some are non blank rows so What I want to do is that display rows where
3rd fields to 11th fields are non blank (in English 3rd field or 4th or
5th.... is non blank)
i hope i am able to explain my point.

I have a table with 2+9 fields.
What does '2+9 fields' mean?
A lot of folks mix logical AND with the english language 'and'.
Your use of 'AND' says that all of the fields must not be null or saying it
another way that no field can be null.
Maybe you want to use 'OR' instead.
--
Build a little, test a little.
:
I have a table with 2+9 fields.
I want to display rows for if [field3] and [field4] and [field5] and
[field6] and [field7] and [field8] is not blank.
I am trying this using Where Clause but it's not working.
I tried
Where (field3 is not null) and (field4 is not null) and (field5 is not null)
and so and so...
Please help.
Thanks- Hide quoted text -

- Show quoted text -

You may also want to think about redesigning your table structure.
Designing a table where you know there are going to be blanks leads to
problems like this. How can you find data if you don't know whether or
not the data is going to be there?

Just a thought.

Keven Denen

 

[kay]

yes i tried using OR but still it gives blank rows for for those fields.
As i have so many blanks.for eg i have around 10000 rows and i have given
critera that display TempId where 'Exception' word is there in those 9 fileds.
So may be there are 9 or 10 counts of 'Exception' in 10000 rows for those 9
fields.
so my purpose is to show rows where this 'Exception' word is found, not
rest of the rows where these 9 fields are blank.

Did you try it using OR instead of AND?

--
Build a little, test a little.

2+9 means - I have values in all rows for first two fields. but for rest 9
fileds (3rd field to 11th field) some rows for all these 9 fields are blank
and some are non blank rows so What I want to do is that display rows where
3rd fields to 11th fields are non blank (in English 3rd field or 4th or
5th.... is non blank)
i hope i am able to explain my point.

I have a table with 2+9 fields.
What does '2+9 fields' mean?

A lot of folks mix logical AND with the english language 'and'.
Your use of 'AND' says that all of the fields must not be null or saying it
another way that no field can be null.

Maybe you want to use 'OR' instead.

--
Build a little, test a little.


:

I have a table with 2+9 fields.
I want to display rows for if [field3] and [field4] and [field5] and
[field6] and [field7] and [field8] is not blank.
I am trying this using Where Clause but it's not working.
I tried
Where (field3 is not null) and (field4 is not null) and (field5 is not null)
and so and so...
Please help.
Thanks

 

[KARL DEWEY]

Ok, there are blanks and there are Nulls. They are not the same. A 'blank'
is a zero lenght string. If you had data in a text field and then deleted
it the results is a zero lenght string, not a null.
Use criteria like this for both Nulls and zero lenght strings ---
Where (field3 is not null or field3 <>"") and (field4 is not null or field4
<>"") and (field5 is not null or field5 <>"")

--
Build a little, test a little.

yes i tried using OR but still it gives blank rows for for those fields.
As i have so many blanks.for eg i have around 10000 rows and i have given
critera that display TempId where 'Exception' word is there in those 9 fileds.
So may be there are 9 or 10 counts of 'Exception' in 10000 rows for those 9
fields.
so my purpose is to show rows where this 'Exception' word is found, not
rest of the rows where these 9 fields are blank.

Did you try it using OR instead of AND?

--
Build a little, test a little.

2+9 means - I have values in all rows for first two fields. but for rest 9
fileds (3rd field to 11th field) some rows for all these 9 fields are blank
and some are non blank rows so What I want to do is that display rows where
3rd fields to 11th fields are non blank (in English 3rd field or 4th or
5th.... is non blank)
i hope i am able to explain my point.

:

I have a table with 2+9 fields.
What does '2+9 fields' mean?

A lot of folks mix logical AND with the english language 'and'.
Your use of 'AND' says that all of the fields must not be null or saying it
another way that no field can be null.

Maybe you want to use 'OR' instead.

--
Build a little, test a little.


:

I have a table with 2+9 fields.
I want to display rows for if [field3] and [field4] and [field5] and
[field6] and [field7] and [field8] is not blank.
I am trying this using Where Clause but it's not working.
I tried
Where (field3 is not null) and (field4 is not null) and (field5 is not null)
and so and so...
Please help.
Thanks

 

[John W. Vinson]

yes i tried using OR but still it gives blank rows for for those fields.
As i have so many blanks.for eg i have around 10000 rows and i have given
critera that display TempId where 'Exception' word is there in those 9 fileds.
So may be there are 9 or 10 counts of 'Exception' in 10000 rows for those 9
fields.

In that case you should not be testing for NULL at all. If you're looking for
records where one (or more) of the fields contain the word "Exception" use a
criterion of

"Exception"

on each of the nine fields; put the criterion on a different row of the query
grid for each one. The corresponding SQL would be

WHERE [Field1] = "Exception" OR [Field2] = "Exception" OR [Field3] =
"Exception"

and so on.

I agree with Karl that your table design may be faulty. What are these nine
fields? Will there *EVER* (COULD there ever) be ten? If so you have a one to
many relationship and should really have this split into two tables, with one
*row* rather than one *field* for each of these nine attributes.

 

[Marco Pagliero]

yes i tried using OR but still it gives blank rows for those fields.
As i have so many blanks.for eg i have around 10000 rows and i have given
critera that display TempId where 'Exception' word is there in those 9 fileds.
So may be there are 9 or 10 counts of 'Exception' in 10000 rows for those9
fields.
so my  purpose is to show rows where this 'Exception' word is found, not
rest of the rows where these 9 fields are blank.

I understand that in most rows ALL 9 fields are blank, and in some row
some field is NOT blank and you want to show only these few rows where
at least one filed is not blank. If it is so:

I never got this syntax working: Where (field3 is not null) or (field4
is not null) or ...

now I write always this way: Where (not isnull(field3)) or (not isnull
(field4)) or (not isnull(field5)) or ...

As Karl says, blanks and Nulls are not the same thing and I think this
is the issue.
So you should test both possibilities: Where (not isnull(field3) or
field3<>"" ) or (not isnull(field4) or field4<>"") or (not isnull
(field5) or field5<>"") and ...

Another possibility (only if the datatype is "text"): where len(field3
& field4 & field5 ... & field11) > 0

Greetings
Marco P

 

[kay]

Thank you so much, its working

Ok, there are blanks and there are Nulls. They are not the same. A 'blank'
is a zero lenght string. If you had data in a text field and then deleted
it the results is a zero lenght string, not a null.
Use criteria like this for both Nulls and zero lenght strings ---
Where (field3 is not null or field3 <>"") and (field4 is not null or field4
<>"") and (field5 is not null or field5 <>"")

--
Build a little, test a little.

yes i tried using OR but still it gives blank rows for for those fields.
As i have so many blanks.for eg i have around 10000 rows and i have given
critera that display TempId where 'Exception' word is there in those 9 fileds.
So may be there are 9 or 10 counts of 'Exception' in 10000 rows for those 9
fields.
so my purpose is to show rows where this 'Exception' word is found, not
rest of the rows where these 9 fields are blank.

Did you try it using OR instead of AND?

--
Build a little, test a little.


:

2+9 means - I have values in all rows for first two fields. but for rest 9
fileds (3rd field to 11th field) some rows for all these 9 fields are blank
and some are non blank rows so What I want to do is that display rows where
3rd fields to 11th fields are non blank (in English 3rd field or 4th or
5th.... is non blank)
i hope i am able to explain my point.

:

I have a table with 2+9 fields.
What does '2+9 fields' mean?

A lot of folks mix logical AND with the english language 'and'.
Your use of 'AND' says that all of the fields must not be null or saying it
another way that no field can be null.

Maybe you want to use 'OR' instead.

--
Build a little, test a little.


:

I have a table with 2+9 fields.
I want to display rows for if [field3] and [field4] and [field5] and
[field6] and [field7] and [field8] is not blank.
I am trying this using Where Clause but it's not working.
I tried
Where (field3 is not null) and (field4 is not null) and (field5 is not null)
and so and so...
Please help.
Thanks