Not Like function

Hi There

I trying to exclude two data in my queries using not like function. The
output is as below;

If I'm using WHERE (((([tbl_[NAME])) Not Like "HA1")) OR (((([tbl_[NAME]))
Not Like "HM1")), both HA1 and HM1 still exist in the output.

If I'm using WHERE (((([tbl_[NAME])) Not Like "HA1")) OR (((([tbl_[NAME])) =
"HM1")) only HA1 are exluded. 

can you guide me please how can I do the right queries to exclude both HA1
and HM1

thank you

-- 
Cheers
Mohsin

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1

0
mohsin
12/5/2009 7:24:38 AM
access 16762 articles. 2 followers. Follow

2 Replies
1210 Views

Similar Articles

[PageSpeed] 36

On Sat, 05 Dec 2009 07:24:38 GMT, "mohsin via AccessMonster.com" <u31851@uwe>
wrote:

>Hi There
>
>I trying to exclude two data in my queries using not like function. The
>output is as below;
>
>If I'm using WHERE (((([tbl_[NAME])) Not Like "HA1")) OR (((([tbl_[NAME]))
>Not Like "HM1")), both HA1 and HM1 still exist in the output.
>
>If I'm using WHERE (((([tbl_[NAME])) Not Like "HA1")) OR (((([tbl_[NAME])) =
>"HM1")) only HA1 are exluded. 
>
>can you guide me please how can I do the right queries to exclude both HA1
>and HM1
>
>thank you

You're making two mistakes.

The LIKE operator uses wildcards. You don't have any wildcards in this query,
so there is no benefit to using LIKE instead of using the = operator - in fact
it will just make your query slower.

Then, you're using OR as if it were an English language conjunction. It isn't.
If the record contains HA1 then the clause

(((([tbl_[NAME])) Not Like "HA1")) 

will be FALSE (it is like "HA1"), but the clause

(((([tbl_[NAME])) Not Like "HM1"))

will be TRUE - HA1 is in fact not like HM1!

The OR operator in Boolean algebra returns TRUE if either or both arguments
are TRUE, so you'll be evaluating 

FALSE OR TRUE

and getting TRUE as a result, so you retrieve the record.

A simpler syntax would just be

WHERE [NAME] NOT IN ("HA1", "HM1")

or

WHERE [NAME] <> "HA1" AND [NAME] <> "HM1"

or

WHERE NOT ([NAME] = "HA1" OR [NAME] = "HM1")
-- 

             John W. Vinson [MVP]

0
John
12/5/2009 7:39:29 AM
Thank you John 


John W. Vinson wrote:
>>Hi There
>>
>[quoted text clipped - 11 lines]
>>
>>thank you
>
>You're making two mistakes.
>
>The LIKE operator uses wildcards. You don't have any wildcards in this query,
>so there is no benefit to using LIKE instead of using the = operator - in fact
>it will just make your query slower.
>
>Then, you're using OR as if it were an English language conjunction. It isn't.
>If the record contains HA1 then the clause
>
>(((([tbl_[NAME])) Not Like "HA1")) 
>
>will be FALSE (it is like "HA1"), but the clause
>
>(((([tbl_[NAME])) Not Like "HM1"))
>
>will be TRUE - HA1 is in fact not like HM1!
>
>The OR operator in Boolean algebra returns TRUE if either or both arguments
>are TRUE, so you'll be evaluating 
>
>FALSE OR TRUE
>
>and getting TRUE as a result, so you retrieve the record.
>
>A simpler syntax would just be
>
>WHERE [NAME] NOT IN ("HA1", "HM1")
>
>or
>
>WHERE [NAME] <> "HA1" AND [NAME] <> "HM1"
>
>or
>
>WHERE NOT ([NAME] = "HA1" OR [NAME] = "HM1")

-- 
Cheers
Mohsin

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1

0
mohsin
12/5/2009 7:56:40 AM
Reply:

Similar Artilces:

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...

Passing an Array created in a Function or Subprocedure back to the calling Subprocedure
I want to have a Function or Subprocedure that can create an array with 52 specific values in it. I then want to call this Function or Subprocedure from a main Subprocedure and be able to access the contents of the array. Everything can be of type variant for the purposes of this exercise. Can anybody help, please? This worked ok for me: Option Explicit Sub testme() Dim myArr As Variant Dim iCtr As Long myArr = BuildArray(myArr) For iCtr = LBound(myArr) To UBound(myArr) MsgBox iCtr & ". " & myArr(iCtr) Next iCtr End Sub Function Buil...

WEEKNUM FUNCTION
I have a column of dates in a table which is manually entered. I am trying to set up another column which then works out the Week Number for the entered date automatically. My problem is that I cannot get the weeknum function to work when I refenence the cell in the other column. All that happens is that I get a #value! error. If the cell I want to reference is E2 I am entering =weeknum("(e2)",2) Can anyone help? Cheers Peter Just a tip, I use the Insert Function dialog box as much as possible. Saves me a lot of typing and syntax errors. Regards, Anders "Ken Wright...

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...

Changing Functional Currency #5
I'm fully aware that once you establish a Functional Currency and have entered transactions, that you can no longer change the FC using the GP UI. However, we still want to change the Functional Currency for 2 of our subsidiaries. What we'd like to do is to create a new company, and set it up with the appropriate currency, and then populate it with all the appropriate settings as the original company. Next, we'd either integrate or import the G/L Transactions (Consisting of over 90% of the activity in the subsidiary company.) What I'd like to know is if there is a lis...

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...

Send/Receive upon SEND of message no longer functional
Until recently, when an originator pressed Send, the message was sent. Now, some users are experiencing transfer of the message to the outbox, and send does not occur until the next log on into Outlook. No changes to the SEND/RECEIVE configuration were made. In fact, the "Send immediately" check box appears to be ignored. This is OUTLOOK 2007, latested SP for Office... "Bradwin" <Bradwin@discussions.microsoft.com> wrote in message news:0C00C871-3FEB-4A09-97EF-54FD07A3663D@microsoft.com... > Until recently, when an originator pressed Send, the mes...

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 ...

How do I use the data consolidate function?
Hello, I have one large spreadsheet that includes several scores for the one item eg: a 1 a 90 a 21 b 2 b 50 c 2 c 2 c 45 c 40 c 1 etc. I would like to know how to use the data consolidate function (in simple terms) to consolidate all the scores for a, b and c for example. So the final score for a=112, b=52 and c=88 I understand I could go through the spreadsheet and highlight the values to get a total for each item but the spreadsheet is quite large, approx 30,000 rows and 2.5 alphabets of columns... I am hoping that the data consolidate function will be useful in this regard....

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...

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...

not functioning
I just installed Micorsoft Office XP Professional from the CD with no problem. However, whenever I click on file, then click on new and then attempt to click on any item listed in the drop down menu I receive a dialog box with Microsoft Outlook and a yellow exclamation point. Also, included in the dialog box are the words could not open the item, try again. Could you please tell me what the problem might be and how to fix it since the program is of no use to me in its current conditon? ...

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...

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...

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 ...

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. -- ----------------...

How do I combine MACROS and functions?
I'm copying and pasting onto excel from an accounting program, and it's pretty inconsistant. Whenever a new heading pops up, it either deposits the first cell in the A column, and the second cell in the C column, or it deposits the first cell in the B column, and the second cell in the C column. I can write a function to identify this =IF(A#="",IF(B#="","","deleteA"),"deleteB") where # stands for whatever row I happen to be in. And I figured out how to write a MACRO to delete a specific cell. But with six thousand rows, and d...

Inability to design functional reports or workflow processes
Standard across the board everyone must want the following capabilities To create a Report whereby All Accounts with no Activities past 2 months can be shown All Accounts with no Opportunities past 6 months can be shown Is there a possibility of including this in a View? (Optimal) Is there any possibility of creating a report with c360? (Workable) At the weekly sales meetings Division Management wants to not only go over the positive but also the negative such as which major Accounts have gone without activity and need to be reassigned to other Salespersons. Chuck: I have tried everything t...

Function or Macro ?
Hi Guys I am using Excel 2000 and Win2000 Pro. I don't know if this should be done in a formula or macro. Basically I need to check the values of three cells on each row. If each cells value comes within range of the specified value then I add the C cell value to the sum value in C8. So in the example below: C1:C3 are added because A1:C1, A2:C2 and A3:C3 all match the criteria. Where as rows A4:C4, A5:C5 & A6:C6 do not, So C4:C6 are not added to the Total Sum in C8. A B C Equal to 99 Starts with 401 ...

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...

Using 'LIKE' with XML Data
I keep some notes in a small XML data file and would like to be able to search them. I am building a filter string: filterString = string.Format("{0} '%{1}%'", "SELECT * FROM " + m_TableName + " WHERE FileName LIKE ", searchString); When testing this produces: "SELECT * FROM tblFileNotes WHERE FileName LIKE '%nero%'" When I then call: DataRow[] foundRows = dtTemp.Select(filterString); I get the error: Missing operand after 'tblFileNotes' operator. The table name is correct as is the field name and the same query works...

IMPLEMENT_SERIAL_T and the like
Hi, I want to implement a template class and use IMPLEMENT_SERIAL_T2 but it throws a whole lot of compile errors. Anybody succeeded using any of these macros intended for template classes? TIA, Stefan ...

If function help required
i need to create an if statement to be ab;le to run a macro when the value is true. i want it the value of cell a1 = 2 then do a macro =IF(A1=2,macro,"false") "i tried to do something but it gives the error -#name?- this has a problem because it doesn't know which macro to run.. i need the format of the if statement.. Hope i made it clear to what i want. George ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ "DeViL_LaW" <DeViL_LaW.wrumy...

Would like to hire a proficient receipt formatter to do my receipt
We would like to hire someone to custom do s few large format PO and Sales Receipts. I am a retailer and not an xml programmer! i have a store logo that includes our store information, and we would like to use our 'colors' instead of the standard yellow. If you are interested in a bit of piecework and know RMS, please let me know how to contact you. Hi Jessica, You can contact our office either by the phone number or URL below. Have a great day! -- Mobitech Lady Amy Luby Mobitech 402.330.0707 www.mobitechonline.com "Jessica at GreenCoast" <JessicaatGreenCoast@...

Editing Source Code in Outlook like in Outlook Express?
This is a multi-part message in MIME format. ------=_NextPart_000_0099_01C4E282.61C801E0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable In Express I've authored an email for my company to email to our = customers. I can edit the source code directly. Can I open an Express = email in Outlook and put my code into it? I haven't found the way to = view and edit source like in Express. Can anyone tell me what I'm overlooking? Thanks, Gary ------=_NextPart_000_0099_01C4E282.61C801E0 Content-Type: text/html; charset="Windo...