How do you do "not in" in access sql? (1 Viewer)

[chas036]

I have a sql server query that I need to use in access 2003 but I can not figure out how to convert the "not in" part of the query. Here is the sql server query

Select * from accounts where beg_date between '1/1/2013' and '12/31/2013' and cast(acctNo,integer) in (2,3,4,5,7,12,20) and acct_type not in ('Individual','User','Viewer')

I can not find how to do the "not in" in access sql.

 

[pr2-eugin]

I believe you can use 'Not In' in Access too. Of course Cast is not a function not recogonized by Access.

 

[jdraw]

Further to Paul's comment, you should enclose date values in #
eg #1/1/2013#
What data type is acctNo?

 

[chas036]

AcctNo is a character. I have no problems with the dates and changing the cast to fit access. My problem is access sql does not recognize "not in"

 

[nanscombe]

The clause 'Not In' is perfectly valid in Access, this should work.

Select * from accounts where beg_date between #1/1/2013# and #12/31/2013# and Val(Nz(acctNo)) In (2,3,4,5,7,12,20) and acct_type Not In ('Individual','User','Viewer')

 

[Mr. B]

It it perfectly acceptable to use Not In("Value1", "Value2", Value3) in Access Sql.
Date values must be formated: #1/1/2013#

 

[pr2-eugin]

My problem is access sql does not recognize "not in"

It sure does work !

SELECT Agents.A_ID, Agents.FirstName, Agents.Password
FROM Agents
WHERE (((Agents.Password) Not In ('1234','Pa55w0rd')));

I get the result.

A_ID    FirstName    Password      
3        Andrew         Muppet10     
9        Caryn          TakeThat1963     
28       Jonathan       Pa55w0rd2     
58       Simon          12345     
67       Christopher    Walcott14

While my data is..

A_ID    FirstName    Password   
1        Abigale        1234
3        Andrew         Muppet10     
7        Barry          Pa55w0rd
9        Caryn          TakeThat1963     
28       Jonathan       Pa55w0rd2     
58       Simon          12345     
67       Christopher    Walcott14

 

[jdraw]

chas036,

For reference, there are a number of tutorials and examples of SQL at w3schools