I want to see records with null values.

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

Guest

I have a form with 10 compobox and I want to make all the compinations.
When I leave the compo emty I want to bring all the values.But when a record
have null fields is not shown in the query. I try the critirion ""is null"
and works okay but when I write this critirio for more than one others field
in the query is not working.
Please help me.

Pantelis
 
Hi,


You have the solution. The SQL syntax is to use IS NULL, or, if you look
at the SQL statement, it should be like:
... WHERE fieldName IS NULL

If you add other fields,

... WHERE fieldName IS NULL AND otherField = 44

or

... WHERE fieldName IS NULL AND otherField IS NULL


and so on. If you use IsNull, in one word, that invokes VBA, but otherwise,
that does the same thing:


... WHERE IsNull( FieldName) AND IsNull( OtherField )



Hoping it may help,
Vanderghast, Access MVP
 
Hi and thank you for your help, but I'm so confused.
Please give me an example. If I have 3 fields A B C in a query
what I have to do to make it work.

Thank you
Pantelis
 
HI,


You add the conjunction, AND ( or OR) and the criteria:

... WHERE field1 IS NULL AND ( Field2 = 44 OR Field3 IS NULL)

or


... WHERE field1 IS NULL AND Field2 = 44 AND Field3 IS
NULL



If you use mixed AND and OR conjunctions, use parenthesis to be sure the
order of evaluation is the one you want.

In the graphical designer, if the criteria are on the same line, that makes
a AND; different criteria lines in the designer are ORed together.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top