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

 

[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!

 

[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!!

 

[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.

 

[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