Not Like function

  • Follow


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
Reply mohsin 12/5/2009 7:24:38 AM

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
Reply 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
Reply mohsin 12/5/2009 7:56:40 AM

2 Replies
621 Views

(page loaded in 0.259 seconds)


Reply: