 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2007Multiple Copies Query
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? 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.  SignatureRegards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ > 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? 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 > Adam > [quoted text clipped - 12 lines] > better > > if it said greater than 1. How would I go about doing this? 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 SignatureDon''t forget to rate the post if it was helpful! Email address is not valid. Please reply to newsgroup only. > Hi Jeff > [quoted text clipped - 22 lines] > > better > > > if it said greater than 1. How would I go about doing this? Adam I usually use Not Null and <> "" as a selection criterion if I want to exclude these. SignatureRegards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ > Hi Jeff > [quoted text clipped - 22 lines] > > better > > > if it said greater than 1. How would I go about doing this? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.