How do I create a query to show records with nulls?

[tomdee]

How do I design a query that returns all records that have one or more null fields in them. Or one that returns records if one of a predetermined subset of the fields is null?
I can't for the life of me work out how to do it so any help would be welcome.

TIA
Tom

 

[The_Doc_Man]

Or one that returns records if one of a predetermined subset of the fields is null?

For each member of the subset of fields, in your query grid, the field is

IsNull( [Member1] ) - which will get labeled Expr1, Expr2, Expr3, etc. for each one you add.

and the criterion is TRUE for each of them. If you have several of these and put TRUE in the same criterion row, that is the OR of the several criteria. So if any one of the selected subset contains a null, it gets included in the returned recordset.

You can also reference the subset member directly in another column, though if it is null, you would probably want it to use Nz([Member1],"") for the "direct" references.

Hope that was clear enough.

 

[tomdee]

Thanks

Hi,
Thanks for your solution, it was very helpful in helping underdstand how queries work. One small problem I had was put the "True"'s on the the same line in the criteria section. It seems to only work properly if they are on different rows.

Many thanks
Tom

 

[Pat Hartman]

Rather than using the IsNull() function, it would be better to use the SQL "IS NULL" syntax. That only requires that you type IS NULL in the criteria field. You don't have to modify two fields.

The criteria in each line is AND'ed and the criteria in different lines is OR'ed. So if you have fld1 IS Null and fld2 IS Null in the same line, BOTH conditions need to be true in order for the row to be selected. I believe you want the condition to be OR so each criteria should go in a separate line.

I attached a picture. The top query AND's the criteria. The bottom query OR's them.

 

[tomdee]

Thanks , that clears it up nicely for me.
Thanks again for your time
Tom

 

[Debased]

I am having a similar problem...I have tried Pat's solution to no avail but I could be just not understanding...

my problem is I have a query based on many sub queries but if any of the subqueries has no records the final query is blank. The condition is not that one field is null and another not null, the condition is that one or several of the queries may have no records at all- which causes the final query to be empty.

helllllllllllppppp!

 

[Pat Hartman]

Whenever you join two tables or queries and one or the other will be missing entries, you need to use an outer join. If you want rows from the left table even when there is no match in the right table use a LEFT join. If you want rows from the right table .... use a RIGHT join.

For example if you wanted a list of all customers including their outstanding balance if any, you would use a left join to join customers to orders because you want the customers even if they have no orders that are outstanding.

 

[Debased]

Excellent! Thank you

 

[Brian1960]

Links

You need to look at the links between the queries. EG All from this and only where equal from the sub. It might helps here.

 

[gigco]

Answers my question

This answers the statement, "Query results that include data from related tables will display only those records in which neither matching field has a Null value."
This statement was in one of my Access books - but offered no solution or reference to fix this issue.

THANKS!!

 

[Pat Hartman]

I understand why you would find that statement confusing. Try this positive statement.

An Inner Join returns only rows where the joined column from tblA has a matching value in the joined column of a row in tblB.

 

[mattbeamish]

New member, similar thread

Sorry to add to an old thread, but I cant see how to generate a new one. I have searched the archive, but not come up with a solution, but Im sure this is so simple that its got to be old hat.

I have a simple db

Tables: Customers, Invoices, Payment
Queries generate Invoice Totals and Payments Totals

I run a 3rd query generating outstanding balances - Invoices Totals minus Payments Totals. This works fine on those who have paid, but where no payments have been made, no balance is calculated. I need the calculation to be performed on all customers even when no payments have been made.



Thanks in advance
Matt Beamish
Leicester, UK.

 

[Pat Hartman]

You need to change your join type. Sounds like the join is currently an Inner join. You need to change it to a left join.

 

[mattbeamish]

Relationship correct

Thanks for your attention. I believe I have specified the relationship correctly as all the customers do appear in the query results (include ALL records from BillsTotal and only those records from QPaymentsTotal where the joined fields are equal) but no value is returned if no payments have been logged - perhaps somewhere, a statment to the effect that no payment = 0 paid. This could be circumvented by entering a zero value for each customer in the payments, but this does not seem very elegant.

 

[Jon K]

I run a 3rd query generating outstanding balances - Invoices Totals minus Payments Totals. This works fine on those who have paid, but where no payments have been made, no balance is calculated.

In your calculation, use the Nz() function on the Payments Total e.g.

[Invoices Total] - Nz([Payments Total])
.

 

[mattbeamish]

Excellent - thankyou very much!
Matt