Problems when deleting data

[Laurel]

I have a table that has a dropdown text column called "membership". If I
enter data into the column, the person is marked as "non-member" or
"associate member." If I don't enter anything, the person is treated as a
"regular member." Most new records are regular members, so it saves on data
entry.

The problem comes when something is mistakenly entered into that column and
then deleted. After that, a query like this.

Select * from tblMembers where (Membership <> +) and (Membership <> ^)

no longer returns any rows. The colum membership looks the same when I look
directly at the table. I assume this has something to do with nulls and
empty strings? What can I do to make my query always work, even if data in
that column has been deleted?

TIA
LAS

 

[John W. Vinson]

I have a table that has a dropdown text column called "membership". If I
enter data into the column, the person is marked as "non-member" or
"associate member." If I don't enter anything, the person is treated as a
"regular member." Most new records are regular members, so it saves on data
entry.

The problem comes when something is mistakenly entered into that column and
then deleted. After that, a query like this.

Select * from tblMembers where (Membership <> +) and (Membership <> ^)

no longer returns any rows. The colum membership looks the same when I look
directly at the table. I assume this has something to do with nulls and
empty strings? What can I do to make my query always work, even if data in
that column has been deleted?

TIA
LAS

Why should Membership contain a + or a ^ character... and how could this be
working at all, since you don't delimit these text strings with " or '
delimiters!?

SELECT * FROM tblMembers WHERE Membership IS NULL;

is probably better; if you have (unwisely, in this instance) set the Allow
Zero Length property of Membership to Yes, you will have to cover the case
where Membership is NULL (empty, undefined, erased) as well as the distinct
case where it contains "" (a zero-length text string, a defined, non empty
value):

SELECT * FROM tblMembers WHERE Len([Membership] & "") = 0;

 

[John Spencer]

There are a few solutions to this.

One:
== Run an update query to set all zero-length strings (zls) to nulls
== Now modify the field to not allow zls.

Two:
== Run an update query to set all null and zls values to "Regular Member".
== Set the field to default to "Regular Member"

Three: Change the criteria in your query to search for both nulls and zls
== to find just members
WHERE Membership = "" Or Membership is Null
== to find non-members
WHERE Membership in ("Associate Member","Non-Member")
or use
WHERE NOT (Membership = "" Or Membership is Null)

By the way, your query makes little sense to me with the + and ^ characters.
Can you explain it? I would think it would generate a syntax error.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County