Blanks fields in tables 2

[encom]

I have a table with existing data that has blanks (nulls). I put in a default value to leave out blanks as there is a need for it. My problem is when I do a report based on that table. How do I ensure that there are no blanks in the report? THis may be the wrong forum, but because I have blanks in the table I thought this would be a good place to start.

 

[PHV]

a table with existing data that has blanks (nulls)
Blanks and Nulls aren't same things.
If you're in SQL, take a look at the IS NULL operator.
If you're in VBA or Expression builder, take a look at the Nz function.
As you say you have default value now for this Null columns you may consider an action query like this:
UPDATE theTable SET theField='theDefaultValue' WHERE theField IS NULL;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Hi,

Sounds like you might need a Where clause that excludes rows...
[tt]
...
Where MyField<>""
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[encom]

Do I need to do that for each field or is there a quicker way?

 

[dhookom]

If you want to filter out any record where any of the fields is Null, then you can set the where clause to:

WHERE IsNull(Field1 + Field2 + Field3 + Field4 + ...)

It's a bit unusual that you should require values but I will assume you know what you are doing.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[willir]

encom

You have a couple of solutions on how to not print the null / blank records / fields.

Going back to PHV's commnet, you may want to investigate why you are getting blanks. Is this because you are not interested in tracking certain information, or is it possible you have a data integrity issue?

For example, if you are missing a phone number or address in a contact database - not a big issue. But if you are missing linked information between two tables, then it can be a big deal.