Query with NOT IN and *

[Guest]

Hi,
I want to create the following query:
SELECT [13].[Division , [13].[RMC Cust code], [13].[RMC Cust desc],
[13].[Budg code], [13].[Budg desc]
FROM 13
WHERE [13].[Budg code] NOT IN ('*REQ', '*RA', '*MNA', '*MA');

But I get error from Acces regard the IN expression.
Can I do it or my only option is to create statment with : like or like or
..... ??
NOT like '*REQ' or not like '*RA' or not like '*MNA' or not like '*MA';

thanks,
Shlomit

 

[Michel Walsh]

You have to use the operator LIKE, and repeat it:

WHERE NOT ( [13].[Budg code] LIKE '*REQ'
OR [13].[Budg code] LIKE '*RA'
OR [13].[Budg code] LIKE '*MNA'
OR [13].[Budg code] LIKE '*MA' )

but since you only look for the 2 or 3 ending characters, you can do:


WHERE NOT Right([13].[Budg code], 2) IN('RA', 'MA')
AND NOT Right([13].[Budg code], 3) IN('REQ', 'MNA')



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

You cannot combine IN and LIKE (use wildcards).

So if you want to use wildcards (Like) then you will have to build your
where statement piece by piece. You would need to use AND and not OR as your
conjunction.
WHERE [Budg Code] NOT LIKE '*REQ' AND [Budg Code] NOT LIKE '*RA' ... AND
[Budg Code] NOT LIKE '*MA'

You could use OR if you move the NOT
WHERE NOT ([Budg Code] LIKE '*REQ' OR [Budg Code] LIKE '*RA' ...OR [Budg
Code] LIKE '*MA' )

By the way, you could combine *MA and *RA as *[MR]A

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..