how do I not display null values on report

  • Thread starter Thread starter Joseph
  • Start date Start date
J

Joseph

good morning

I have a report that shows a list of 97 'categories' pulled from a table
titled 'categories. I had performed a test to see if I could add new
'categories' to this list and then went back and deleted the entry. After
inputting the list of 97 into the table, I found that record number 48
(numbered by autonumber function) was incorrect and I deleted the record.
However, when I run the report, it displays a blank row at the top of the
list of the other 96 categories. At first I did not notice this, until I
created the "enter new category" form, I performed a test to see if I entered
in a new 'category' would it populate in the table, I then went back and
deleted the record ... when I run the report I now have two blank spaces at
the top of the report ... how do I tell ACCESS to not show 'categories' with
a null value?

thank you
 
You have deleted the recordset -just a part of it - or it would not show in
the report. Are there any linked tables with related records ? If so you
need to delete them as well (if this are in the report data)
 
Thanks Wayne, actually, I selected the filter option to "does not equal
blank" and that did the trick ...

thank you for the reply though
 
thank you ... my 'category' table is not linked, however, the field for
"category" does appear in other tables ...
 
Suppressing Nulls will solve the problem as far as the report is concerned,
but it sounds to me as though the fundamental problem is that you have not
deleted the records, but only the values in them, so you still have two rows
in the table with Nulls. To delete a record use the Delete Record button on
the toolbar, rather than deleting the value from the field.

You can 'clean up' by running a Delete query to remove all rows with Null in
the Category column:

DELETE *
FROM Categories
WHERE Category IS NULL;

Note that you must use 'IS NULL' not '= NULL'. A Null is not a value, but
the absence of a value, so nothing can be equal to Null, not even another
Null. The result of testing for something being equal to Null will itself be
Null, neither True nor False. You can also use the IsNull function to test
for Null.

On the other hand you can use '= NULL' where the = sign is an assignment
operator rather than the equality operator, i.e. you can make a field or
control Null in this way.

Ken Sheridan
Stafford, England

Ken Sheridan
Stafford, England
 
Joseph

To follow on from Ken's point - you should also note that unlinked record
whilst not really being a big problem can cause some application dificulties.

Say you have 2 tables
tblPeople
tblPayments

Say John Smith has made 5 payments

If you just delete John Smith's record then you will have 5 payments that no
one made ?
 
Thank you Ken, Wayne,

I deleted the records using the delete record button and that did the trick
(as far as the report is concerned) ... I was able to click off the toggle
filter and there was no population of null space

Thanks again
 
Back
Top