How can I avoid NULLs by default?

[Guest]

How can I ensure that blank fields do not contain NULL values?

The reason I want to achieve this is that I use an "N" in a field to cut out
records that I don't want included in a mailing query. The query has <>"N"
as the criteria. Most of the records have a blank in this field; but,
unknown to me, eight out of 900 records had NULL in place of blank!

I expected that NULL would not be equal to "N" but <>"N" did not include the
records with NULL in this field. So my mailing list has excluded 8 records
that I expected to be used. I only discovered this by accident - should
listen to my own advice about rigorous testing!! I could use the Nz function
in the query but this seems like a complication. I don't know how just 8
records acquired a NULL value - but I want to make sure it doesn't happen
again!

 

[Ken Snell \(MVP\)]

Null value can be entered into a field when a user deletes the data that
were in that field and doesn't re-enter a value. The way to avoid this is to
set the Required property for that field to Yes in the table's design view,
and to set the Allow Zero-Length Strings property to Yes as well.

And/or you can modify your query's Where clause:

WHERE FieldName <> 'N' OR FieldName Is Null

 

[Tom Wickerath MDB]

don't allow nulls or blank

Access MDB has 'allow nulls' and 'allow empty strings'

real databases like SQL Server don't make a distinction between the two

 

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.