Query with <> not working

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

Hi,

OS - XP Pro SP3
Access - XP Office Pro - SP3

This is a simple issue and I'm embarrased to ask the question, but here it is.

I have a single table called MailingList. There is a text field call
MemberTerm. It has a value of A-Annual, E-Expired, H-Honorary, L-Life, N-New
or nothing. I imported the data from an Excel spreadsheet. Anyhow.

When I run the following query:
SELECT MailingList.PostNo, MailingList.FirstName, MailingList.LastName,
MailingList.MemberTerm
FROM MailingList;

I get 468 records, which is correct.

When I run the following query:

SELECT MailingList.PostNo, MailingList.FirstName, MailingList.LastName,
MailingList.MemberTerm
FROM MailingList
WHERE (((MailingList.MemberTerm)="E"));

I get one entry (which is a blank record), which I guess is technically
correct for a query.

When I run the following query:

SELECT MailingList.PostNo, MailingList.FirstName, MailingList.LastName,
MailingList.MemberTerm
FROM MailingList
WHERE (((MailingList.MemberTerm)<>"E"));

I get 224 records.

I would have thought that since no records have an "E in MemberTerm that I
should get 468 records.

What am I missing here? If the total count is 468 records and no records
have MemberTerm = "E", then all records should be included in MemberTerm <>
"E" set, right? Obviously my logic is faulty here.

I appreciate any help.
 
There are likely 244 records where the MemberTerm field is Null. Null
is an unknown value and therefore cannot be compared. It is neither equal,
nor unequal, to anything. So your query ignores those records and just
returns the ones that have a value that is <> "E". If you want to return
the other 244, you will need to use;

Where MailingList.MemberTerm <> "E" OR MailingList.MemberTerm Is Null
 
Ah, you were absoulutely correct. This works GREAT! I'm am going to have
to get a real good understanding of Null.

When I setup my data entry screens, should I set the variable default value
to ""? Would that make life easier or does that introduce new problems?

I like to program to avoid problems later down stream. Or is the proper way
to deal with Access is learn to process Null and nothing the proper way?

--
Dennis


Beetle said:
Or you could use the Nz function;

Where Nz([MemberTerm], "") <> "E"
--
_________

Sean Bailey


Dennis said:
Hi,

OS - XP Pro SP3
Access - XP Office Pro - SP3

This is a simple issue and I'm embarrased to ask the question, but here it is.

I have a single table called MailingList. There is a text field call
MemberTerm. It has a value of A-Annual, E-Expired, H-Honorary, L-Life, N-New
or nothing. I imported the data from an Excel spreadsheet. Anyhow.

When I run the following query:
SELECT MailingList.PostNo, MailingList.FirstName, MailingList.LastName,
MailingList.MemberTerm
FROM MailingList;

I get 468 records, which is correct.

When I run the following query:

SELECT MailingList.PostNo, MailingList.FirstName, MailingList.LastName,
MailingList.MemberTerm
FROM MailingList
WHERE (((MailingList.MemberTerm)="E"));

I get one entry (which is a blank record), which I guess is technically
correct for a query.

When I run the following query:

SELECT MailingList.PostNo, MailingList.FirstName, MailingList.LastName,
MailingList.MemberTerm
FROM MailingList
WHERE (((MailingList.MemberTerm)<>"E"));

I get 224 records.

I would have thought that since no records have an "E in MemberTerm that I
should get 468 records.

What am I missing here? If the total count is 468 records and no records
have MemberTerm = "E", then all records should be included in MemberTerm <>
"E" set, right? Obviously my logic is faulty here.

I appreciate any help.
Click to expand...
Click to expand...
 
I'd say the proper way is to learn how to process Null correctly.

There are significant differences between Null and Zero-Length Strings ("").

Null is appropriate when you do not know whether or not the field has a
value, whereas a ZLS is appropriate when you know that the field doesn't
have a value. For example, if you don't know what my telephone number is,
you'd leave the PhoneNumber field Null. If you subsequently found out that I
don't have a telephone, you might then set it to a ZLS. It's similar for
numeric fields (except that it would be a difference between using Null and
0)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dennis said:
Ah, you were absoulutely correct. This works GREAT! I'm am going to
have
to get a real good understanding of Null.

When I setup my data entry screens, should I set the variable default
value
to ""? Would that make life easier or does that introduce new problems?

I like to program to avoid problems later down stream. Or is the proper
way
to deal with Access is learn to process Null and nothing the proper way?

--
Dennis


Beetle said:
Or you could use the Nz function;

Where Nz([MemberTerm], "") <> "E"
--
_________

Sean Bailey


Dennis said:
Hi,

OS - XP Pro SP3
Access - XP Office Pro - SP3

This is a simple issue and I'm embarrased to ask the question, but here
it is.

I have a single table called MailingList. There is a text field call
MemberTerm. It has a value of A-Annual, E-Expired, H-Honorary, L-Life,
N-New
or nothing. I imported the data from an Excel spreadsheet. Anyhow.

When I run the following query:
SELECT MailingList.PostNo, MailingList.FirstName, MailingList.LastName,
MailingList.MemberTerm
FROM MailingList;

I get 468 records, which is correct.

When I run the following query:

SELECT MailingList.PostNo, MailingList.FirstName, MailingList.LastName,
MailingList.MemberTerm
FROM MailingList
WHERE (((MailingList.MemberTerm)="E"));

I get one entry (which is a blank record), which I guess is technically
correct for a query.

When I run the following query:

SELECT MailingList.PostNo, MailingList.FirstName, MailingList.LastName,
MailingList.MemberTerm
FROM MailingList
WHERE (((MailingList.MemberTerm)<>"E"));

I get 224 records.

I would have thought that since no records have an "E in MemberTerm
that I
should get 468 records.

What am I missing here? If the total count is 468 records and no
records
have MemberTerm = "E", then all records should be included in
MemberTerm <>
"E" set, right? Obviously my logic is faulty here.

I appreciate any help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top