Not Like - not working in query criteria

I have several thousand rows of data with various values (CL, A1, A2, Null).  
When I put the criteria - Not Like "CL" in the query criteria, it doesn't 
return any rows (and I know there are rows that don't have "CL" in them).  If 
I put Is Null or Not Like "CL", it will return the blank rows.  I want to 
return the other values (A1, A2 and Nulls), but I can't get it to return any 
records when I just say Not Like "CL" or Not In ("CL").  This should be 
simple, and I'm sure I'm overlooking something....any help is appreciated!  
Thanks!
-- 
Jen Scott
0
Utf
11/24/2009 9:53:05 PM
access 16762 articles. 3 followers. Follow

2 Replies
2320 Views

Similar Articles

[PageSpeed] 16

You gotta use a wildcard with Like  -   Not Like "CL*"     OR    Not Like 
"CL?"  

-- 
Build a little, test a little.


"Jen Scott" wrote:

> I have several thousand rows of data with various values (CL, A1, A2, Null).  
> When I put the criteria - Not Like "CL" in the query criteria, it doesn't 
> return any rows (and I know there are rows that don't have "CL" in them).  If 
> I put Is Null or Not Like "CL", it will return the blank rows.  I want to 
> return the other values (A1, A2 and Nulls), but I can't get it to return any 
> records when I just say Not Like "CL" or Not In ("CL").  This should be 
> simple, and I'm sure I'm overlooking something....any help is appreciated!  
> Thanks!
> -- 
> Jen Scott
0
Utf
11/24/2009 10:16:01 PM
The Like operator is used with wildcards. If you didn't want to se CLEAR and 
CLEAN then Not Like "CL*" would work. Notice the * wild card.

What you want is not equal to CL or Null. Below is the syntax.

<> "CL" or Null
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Jen Scott" wrote:

> I have several thousand rows of data with various values (CL, A1, A2, Null).  
> When I put the criteria - Not Like "CL" in the query criteria, it doesn't 
> return any rows (and I know there are rows that don't have "CL" in them).  If 
> I put Is Null or Not Like "CL", it will return the blank rows.  I want to 
> return the other values (A1, A2 and Nulls), but I can't get it to return any 
> records when I just say Not Like "CL" or Not In ("CL").  This should be 
> simple, and I'm sure I'm overlooking something....any help is appreciated!  
> Thanks!
> -- 
> Jen Scott
0
Utf
11/24/2009 10:19:02 PM
Reply:

Similar Artilces:

Add new record based on form query
Hi All, I have a form based on a query which pulls from one table, and also has 2 unbound field that gatherd input from the user (qty). I need to have the values from this form written to a table as a new record. I have a button which is runs a macro: Here is what the macro does: OpenTable ||| TaskLineItems, Add GoToRecord ||| new setValue ||| [TaskLineItems]![ChargeCat], [Forms]![f_PullItems_2]! [Category] (repeat setValue for all fields) But I get an error on this "The Object doesn't contain the Automation object "TaskLineIt...

Why set_terminate is not working in MFC under VC 7.0
Cast your mind back to this post from April last year: http://groups.google.co.uk/groups?hl=en&lr=&ie=UTF-8&selm=x5Rn5jdBDHA.1120%40cpmsftngxa06.phx.gbl > >> Does any body know why set_terminate not working in MFC 7.0? > > I took an extensive look at this. I can reproduce the symptoms you > are seeing. We'll get back to you shortly. > It seems quite a number of people have had this problem too, including us, yet I can't find a public solution anywhere. Please share the solution with the newsgroup. Regards, Dave Elcock. ...

I would like to
I would like to have the Sales rep printed on the receipt, how do I accomplish this? Craig, Download the Receipt - 40 column with Sales Rep from here; http://tinyurl.com/4gdbu You must have access to CustomerSource. -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Management System on the right. http://tinyurl.com/75bgz ********** &qu...

Missing import filter for Word! Like "WordPerfect 5.1 (UNIX)" ...
I'm missing much import filter on Office-Mac (specially WordPerfect 5.1 UNIX for Word.mac). Office 95/97/2000/XP for windows have much more useful import filter. Why all this filter are missing in Office.mac????? Or are all them included in an additional package????? Hi Dieter, This question has been answered over and over again in the Macintosh Word newsgroup. Take a gander in that newsgroup for your answer. Office does not ship with a converter because a converter has not been built that does a good job with converting. Details are here: http://www.mvps.org/word/FAQs/General/WordP...

Access 2000 query help (please!?)
Hi there guys, I am trying to devise a query and its just not coming to me. I'm hoping someone out there can help. I am trying to create a query that will give me the distinct value from two columns in the same table. Here is what its like: Table1: Schools Columns: school_id, school_abbrev, school_name Table2: Travel_Log Columns: log_to, log_from (these are numerics that tie to school_id above) So I want to create a list of all the schools visited in the travel log, so I want the distince values of log_to and log_from and then list them out with their abbreviations and full names i...

Macro Help
I need a macro that will insert a number of rows based on the number of months between a start and end date and then copy the information in the above row to the new rows. The records are over 8,350.. so ill also need some idea how to get it to stop when it fills the worksheet so i can transfer those into another workbook What version of Excel are you using? "Katerinia" <Katerinia@discussions.microsoft.com> wrote in message news:394E5E6B-C761-4D83-87BC-B0A88AA41BAF@microsoft.com... >I need a macro > that will insert a number of rows > based on the...

Making Excel act like a database.
I need some help on a common problem in our company. We have several clients that we have agreed to do some Excel 2003 spreadsheets for. The spreadsheets analyze our performance for them. These spreadsheets follow a very specific formatting that the clients have gotten use to and are unwilling to change. When we have to update the spreadsheets once a month, it can take all day to do the updating for each client because we are currently using copy and paste to get the data in the right cells. I'll explain more in a second, but what I am hoping to find out from this posting is alternate...

COMBINING CELLS WITH LIKE DATA
I HAVE A SPREADSHEET TO TRACK INTERNAL MACHINING SCRAP SIMILAR TO THE ONE BELOW. I AM LOOKING FOR A WAY TO INSTANTANIOUSLY COMBINE ALL THE TOTALS FOR CUSTOMER A OR PART 13112 OR MACHINE B9 ON ANOTHER SPREADSHEET IF ANYONE KNOWS OF A WAY TO DO THIS PLEASE LET ME KNOW. THANKS. DATE CUSTOMER PART MACH # QTY TOTAL 8/9/2004 A 111 B9 2 13.46 8/9/2004 S 123 B9 4 26.92 6/4/2004 S 123 C14 5 31.95 6/1/2004 M 2131 C14 12 29.88 7/22/2004 B 13123 C3 1 0.75 7/5/2004 A 12312 C4 1 0.75 7/5/2004 ...

My Headers are Spam Like
I have an Exchange 5.5 server on NT domain, the headers on messages say: received from <machine name>.<domain name> which is SERVERA.COMPANYB, and some sites are rejecting our mail as spam. I think it's a dns problem because SERVERA.COMPANYB doesn't resolve to anything. Is there any way I can get a .COM into that header? dlw wrote: > I have an Exchange 5.5 server on NT domain, the headers on messages > say: received from <machine name>.<domain name> which is > SERVERA.COMPANYB, and some sites are rejecting our mail as spam. > I think it's a...

Reminder is not work in default folder #2
I used as follow coding to send email to outlook client(outlook 2000): ''''''''''''''''''''''''''''''=AD'''''''''''''''' Dim ll As New CDO.Message Dim bb As New CDO.Configuration bb.Fields(cdoSMTPServer) =3D "nc80012" bb.Fields(cdoSendUsingMethod) =3D cdoSendUsingPort bb.Fields.Update Set ll.Configuration =3D bb ll.Fields.Append "urn:schemas:mailheader:x-mess=ADage-flag", adBSTR _ ...

Can I view image attachments in Outlook like they appear in Outlook Express?
Can I view image attachments in Outlook like they appear in Outlook Express, where they appear underneath the message body? Alternatively, can I "preview" them, rather than having to start up my image editing program just to see them? Thanks. No you can't view them inline. Have you tried changing the default program for opening images to your preview program so that it starts up automatically when you double click on an image attachment? "tasbill" <bill_griffin1@hotmail.com> wrote in message news:02c501c393c4$d8bc5480$a001280a@phx.gbl... > Can I view ima...

How do I protect work sheet against the "Delete" function?
I have a shared worksheet full of textual data (names). I want the users to be able to freely move the data around the sheet by simple pointer movement or through cut and paste. However, deleting any data (even if immediate re-entering elsewhere is intended) must not be allowed in any circumstance. Is this possible? If so, how? Thanks Kevin Nurse If I had to do this, I think I'd provide a macro that did exactly what I wanted done and no more. I'd protect the worksheet and then ask the user to select the from range and to range and do the copy in code (also unprotecting and th...

Send Brochure Electronically like It appear as if in print
I am re-posting this because someone replied to send as an attachment, but that doesn't have it appear as a 3 page panel brochure- does someone know how to do this (see below) I want to save my brochure so that it is not just 2 pages but looks like a 3 panel brochure that I would print -but I want to include it in an email and have it look like a 3 panel brochure is that possible. I was having trouble publishing to web-put I don't want to publish to a web page, just have someone who I want look at this brochure in electronic form but look at it as it would be if they received it if I...

M2003, suddenly stopped working
M2003 has been working fine for the last couple of years and then suddenly today it starts up, I get the logon screen but when I try and login(using passport or not) it says that Money is not setup correctly. Any ideas? -Euan Can you create a new file or open the sample file? What is the entire error? I'm inclined to think (with the piece of the error that you gave) that a reinstall may be in order. "Euan Garden[MS]" <euang@online.microsoft.com> wrote in message news:%23Pce7N2rEHA.868@TK2MSFTNGP12.phx.gbl... > M2003 has been working fine for the last couple of...

Access 2007 LIKE Expression
I am converting an Oracle database to Access. When recreating my queries I cannot figure out the syntax for the LIKE expression. Oracle: SELECT ORDNUM, CUST_LNAME, SALE_DATE FROM ORDERS WHERE UPDATED_BY LIKE '%APRIL%'; Access: SELECT orders.ORDNUM, orders.CUST_LNAME, orders.SALE_DATE FROM orders WHERE (((orders.UPDATED_BY) like "% APRIL %")); I have hacked the syntax in every way I can think of using information from various posts on this web site but it always returns zero rows. No errors, but no data either. I have verified the data is in the table. Microsoft ...

Live Streaming not working in Media player in Vista
I am not able to watch the live streaming using WMP 11. It was working fine with Vista before. It still works when I use my another machine which has XP on it. When I click play it says (connecting to media...) and after some time it goes back to "Ready". I read somewhere that Vista's video driver update is clashing with media player. Is there any solution to this problem? ...

Netfolders-like capabilities and Outlook 2003
I know Netfolders has been discontinued in Outlook 2003, but is it discontinued or just hidden? That is, not part of the default installation. And can it be installed if you just look for it? B/c for all of its hassles, my wife and I use NetFolders as part of our business, and it works 99% of the time. Perhaps every 3 years or so we have to set the folders up for sharing again. We've been using NetFolders since 2000, and it's crapped out only once. But if anyone has found a good solution that's not too expensive (under $100) I would be glad to hear about it. -- ----------------...

Excel hiding everything except the work on the sheet?
Hi, I am wondering if there is a quick way to hide all the columns outside the rows and columns not used in my spreadsheet? Currently I do this by selecting and dragging but the rows take a while to get to the end. Is there an easier way to do this? I am using excel 2003 TIA Patrick CRTL + Right arrow will slect to comumn IV Format>Columns>Hide will hide these. CRTL + Downarrow for rows. Gord Dibben Excel MVP On Mon, 14 Jun 2004 19:04:00 -0700, "Patrick" <patrickm@spamsonic.net> wrote: >Hi, > I am wondering if there is a quick way to hide all the columns ou...

Working with names
I'm working in a worksheet plenty of named ranges. When I change a name of a range or cell, the formulas referring that range don't reflect tha change of the name (they keep the old name). May you suggest me a procedure to reflect automatically those changes? No, Excel doesn't change your formulas when you change a name. In fact it doesn't have a "change a name" feature as such. You must accomplish that by selecting the range and defining the new name. At that point you have two named ranges, the original and the new one. Then you would delete the old one from ...

VPN Fails Via WiFI but works via Wire?
Hi, I have a number of clients whose staff work from home and access the SBS VPN. The vast majority can get connected without a hitch but quite often I will get ones who use Wifi connections from their laptops and can't seem to connect or have a really hit or miss time with getting the VPN to connect. On windows Server 2008 systems I just manually create the VPN on the client's laptop as per the instructions with SBS 2008 and I often find that they can't connect when accessing their router via a plugged in cable, but can't when going via wireless. Is there ...

so now my virtual machine is not working.
in installing drivers for the scanner I was restarting vitual xp a lot of times. now I have a message that virtual integration features are disabled and need to shut down virtual machine and start it again. the restart does not work on the screen like a dos screen. and using the control alt delete button does not shut it down. I have also restarted win7 but still can't get the virtual machine going... can anybody give me a clue as to what to do now? charlie tool/settings then choose shutdown method. the only one that worked finally was the 'turn off' butto...

Comcast Doesn't like Outlook; likes Outlook Express
About once a month, I can't get mail through Outlook XP, even though the pop3 and smtp servers are up. I call support, they tell me they don't support Outlook, just Outlook Express. Sure enough, I set it up, and OE works fine when O won't. What is the internal difference that makes this so? I'm having to use OE as I can't afford to lose email. Thanks, Britt Dickson My Outlook (versions from 2000 through 2007) work fine with Comcast. = Tell us what is different between account settings in OE and Outlook - = we can't look over your shoulder. --=81 Milly Staples...

Shell not always working
i'm using VBA and the Shell function to call Excel from Excel. on some PCs it works fine and others it does not. all are using the same version of Office. any clues? On Wed, 9 May 2007 17:00:04 -0400, mcnewsxp wrote in microsoft.public.excel: >i'm using VBA and the Shell function to call Excel from Excel. >on some PCs it works fine and others it does not. >all are using the same version of Office. >any clues? It would help if you showed the code you used, and the environment (OS and Excel versions) where it works and where it doesn't. -- Michael Bednarek http...

Update Queries not running.
Experts, I created a function to run multiple update queries in my application... it worked fine with 2 update queries, however it stopped responding when I try running it with 4 update queries. Is there a limitation on how many update queries an application may have: Function RunUpdateQueries() As Boolean On Error GoTo ErrorHandler Dim strQuery As String strQuery = "qryUpdateSUM1" DoCmd.OpenQuery strQuery DoCmd.Close strQuery = "qryUpdateSUM2" DoCmd.OpenQuery strQuery DoCmd.Close strQuery = "qryUpdateSUM3" DoCmd.OpenQuery strQuery DoCmd.Close strQuery =...

what afxMessageBox not working here
I have CV2App class derived from CWinApp and I invoke a dialog box with CV2Dlg class derived from CDialog. In BOOL CV2App::InitInstance() after user clicks on dialog Ok button i need to display the AfxMessageBox with the string from a list box but the messagebox doesnt show up. I debuged and found it going in the following if (nResponse == IDOK) { CString msg ; msg = dlg.m_selected; //m_Selected is a public variable AfxMessageBox(msg); } Here is snaps of my code BOOL CV2App::InitInstance() { AfxEnableControlContainer(); // Standard initialization // If you are not using these...