Multiple Copies Query

[Adam]

I have a database set up for our company newsletter. I have a query that
allows me to see how many people receive multiple copies. The "copies" column
in this query displays numbers like 2, 20, 35 etc. However when I opened it
today there are records where the copies cell is blank. How can this be? In
the design view the criteria says "Is not null." Perhaps it would be better
if it said greater than 1. How would I go about doing this?

 

[Jeff Boyce]

Adam

We humans have trouble seeing the difference between a Null and a
zero-length string (""). You could try running a query against that table
to see if there are any ZLS's in that field.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Adam]

Hi Jeff

Thanks very much! I ran the query you suggested and it showed all the
records I DON'T want in my multiple copies query (i.e ones that I thought had
a null value but actually have a ZLS. So how do I ensure that this query only
has records where the entry in the "copies" column is >1?

Thanks again
Adam

 

[Dale Fye]

Adam

WHERE Len([yourField] & "") > 0

This problem arises when someone deletes data from a field (generally by
highlighting the cell in a table for query view and hitting the delete key,
or by deleting the data from a textbox on a form). When that happens, you
are left with a ZLS rather than a NULL.

Over time, I've gotten to where I generally use this method whenever I want
to check to see if the field is "empty".

HTH
Dale

 

[Jeff Boyce]

Adam

I usually use Not Null and <> "" as a selection criterion if I want to
exclude these.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/