Using a query to find a null value in a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to use a select query to find all of the fields in a table that
are a null value. Can I do this with one query, or do I need to create
multiple queries, or do I have to use VBA to do this.

Any help is appreciated.
 
You can use a select query. If you are using the query builder, use the
Criteria rows. In the column for each field you want to check, type in Is
Null. But, don't put them all on one row. Everything on a row is an AND
condition. Put them all on separate rows. When you use multiple rows, each
row is an OR condition.

If you are using SQL, then you need to string OR conditions for each field
you want to check in the WHERE predicate:

WHERE [field1] Is Null OR [field2] Is Null OR [field3] Is Null

The result of the query will produce a row for each record that has one or
more null values in a field, but you will have to visually identify which
field(s) in each row is null.
 
It depends on what you want.

You can use the criteria IS NULL against each of the fields in an or clause
and that will return all the records where any of the fields is null.

In the query grid, you would have to enter the Is Null criteria on separate
lines in criteria area.

SELECT FieldA, FieldB
FROM YourTable
WHERE FieldA is Null Or FieldB is Null
 
Your question doesn't provide any direction on how you want to display the
results. Do you want to display the entire record where any field contains a
null? Or, do you want to see a record ID and field name where the field is
null?
 
I would like to use a select query to find all of the fields in a table that
are a null value. Can I do this with one query, or do I need to create
multiple queries, or do I have to use VBA to do this.

Any help is appreciated.
Click to expand...

Very simple query with a specific syntax: use a criterion

IS NULL

on the field. The word IS is essential -

=Null

will NOT work, because nothing is equal to NULL (or unequal to it
either).

John W. Vinson[MVP]
 
This is exactly what I was looking for. I didn't know I could use an OR in
the select query. Thanks to all for the help.

Klatuu said:
You can use a select query. If you are using the query builder, use the
Criteria rows. In the column for each field you want to check, type in Is
Null. But, don't put them all on one row. Everything on a row is an AND
condition. Put them all on separate rows. When you use multiple rows, each
row is an OR condition.

If you are using SQL, then you need to string OR conditions for each field
you want to check in the WHERE predicate:

WHERE [field1] Is Null OR [field2] Is Null OR [field3] Is Null

The result of the query will produce a row for each record that has one or
more null values in a field, but you will have to visually identify which
field(s) in each row is null.

AnubisAscends said:
I would like to use a select query to find all of the fields in a table that
are a null value. Can I do this with one query, or do I need to create
multiple queries, or do I have to use VBA to do this.

Any help is appreciated.
Click to expand...
Click to expand...
 
Back
Top