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!

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


--

Ken Snell
<MS ACCESS MVP>



"RGUBTON" <(E-Mail Removed)> wrote in message
news:F4A8ED48-B2F8-4998-80B0-(E-Mail Removed)...
> 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!
>

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



"RGUBTON" <(E-Mail Removed)> wrote in message
news:F4A8ED48-B2F8-4998-80B0-(E-Mail Removed)...
> 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!
>

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/ex...ributors2.html for contact
info.


"Tom Wickerath MDB" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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