Null Value Records

[Guest]

I have a query that creates a table with 7 total columns. What I would like
to do is create that table so that if 4 specific fields of a record are null,
they will not appear in the table.

For example:

ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF ColumnG


I need something that will not show the record if columns C,D,E, and F, are
null. There are cases where one column lets say D will have data but not C,E,
or F. I want these records included. ONLY IF ALL 4 COLUMNS ARE NULL do I want
to exclude the record.

Hope that explains it.

Thanks in advance for your replies.

Bill

 

[Tom Ellison]

Dear Bill:

Having a row "not appear in the table" sounds like something quite unlike
"not show[ing] the record if columns . . . are null.

In either case, the logic could be a portion of your WHERE clause:

WHERE (ColumnC IS NOT NULL OR ColumnD IS NOT NULL OR ColumnE IS NOT NULL, OR
ColumnF IS NOT NULL)

I put this in parens so it can more easily be combined with any other
criteria you might need.

If you have any other criteria, doing this in the query designer can be a
huge mess. You may find it far better to work in the SQL view.

Tom Ellison

 

[Michel Walsh]

Hi,



INSERT INTO ... SELECT ... WHERE -3 >= IsNull(field1) + IsNull(field2) +
IsNull(field3) + .... + IsNull(field7)



If the field is null, IsNull returns -1; otherwise, it returns 0. So, if 4
fields or more return -1, the WHERE clause will disregard the record BEFORE
making the INSERT.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Actually it's more efficient to AND these criteria not OR and NOT them:

WHERE (ColumnC IS NULL AND ColumnD IS NULL AND ColumnE IS NULL AND ColumnF
IS NULL)

Dear Bill:

Having a row "not appear in the table" sounds like something quite unlike
"not show[ing] the record if columns . . . are null.

In either case, the logic could be a portion of your WHERE clause:

WHERE (ColumnC IS NOT NULL OR ColumnD IS NOT NULL OR ColumnE IS NOT NULL, OR
ColumnF IS NOT NULL)

I put this in parens so it can more easily be combined with any other
criteria you might need.

If you have any other criteria, doing this in the query designer can be a
huge mess. You may find it far better to work in the SQL view.

Tom Ellison

I have a query that creates a table with 7 total columns. What I would like
to do is create that table so that if 4 specific fields of a record are
null,
they will not appear in the table.

For example:

ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF ColumnG


I need something that will not show the record if columns C,D,E, and F,
are
null. There are cases where one column lets say D will have data but not
C,E,
or F. I want these records included. ONLY IF ALL 4 COLUMNS ARE NULL do I
want
to exclude the record.

Hope that explains it.

Thanks in advance for your replies.

Bill

 

[Michel Walsh]

Hi,


Invert the comparison.

 

[Guest]

I understand what your saying, but I'm not sure how to code it. The example
you gave here, is not working in my sql. Could you explain in more detail how
to code it into the SQL?

Thanks

 

[Guest]

This would not work as if there were data in one of the fields, it would not
show the WHOLE record.

Actually it's more efficient to AND these criteria not OR and NOT them:

WHERE (ColumnC IS NULL AND ColumnD IS NULL AND ColumnE IS NULL AND ColumnF
IS NULL)

Dear Bill:

Having a row "not appear in the table" sounds like something quite unlike
"not show[ing] the record if columns . . . are null.

In either case, the logic could be a portion of your WHERE clause:

WHERE (ColumnC IS NOT NULL OR ColumnD IS NOT NULL OR ColumnE IS NOT NULL, OR
ColumnF IS NOT NULL)

I put this in parens so it can more easily be combined with any other
criteria you might need.

If you have any other criteria, doing this in the query designer can be a
huge mess. You may find it far better to work in the SQL view.

Tom Ellison

I have a query that creates a table with 7 total columns. What I would like
to do is create that table so that if 4 specific fields of a record are
null,
they will not appear in the table.

For example:

ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF ColumnG


I need something that will not show the record if columns C,D,E, and F,
are
null. There are cases where one column lets say D will have data but not
C,E,
or F. I want these records included. ONLY IF ALL 4 COLUMNS ARE NULL do I
want
to exclude the record.

Hope that explains it.

Thanks in advance for your replies.

Bill

 

[Guest]

I just saw this post. Works great now! THANKS!

Hi,


Invert the comparison.