Multiple criteria - one of them to exclude several codes.

F

FA

Hello,

I have the following SQL:

SELECT ETS.*
FROM ETS
WHERE (((Left([COMM CODE],8)) Not In (73121010)) AND ((Left([COMM CODE],6))
In (732620))) OR (((Left([COMM CODE],4)) In
(7213,7217,7221,7223,7227,7229,7312,7313,7314,7317,8311)));

COMM CODE has 10 digits but my criteria needs to be less digits because they
refer to different groups.

I can't make the "NOT IN" to work. I also tried OR between that criteria
and the next, also <> instead of not in. Every time I go to datasheet and I
still find the 73121010 code!
What is that I 'm doing wrong.

Maybe I could have use COMM CODE without the left formula and use *?

Please help!
 
K

Klatuu

You are explicitly asking for those records in the OR side of your criteria.
7312 is listed.
 
F

FA

I know but how should I ask it to give those in 7312 except 73121010?

Klatuu said:
You are explicitly asking for those records in the OR side of your criteria.
7312 is listed.
--
Dave Hargis, Microsoft Access MVP


FA said:
Hello,

I have the following SQL:

SELECT ETS.*
FROM ETS
WHERE (((Left([COMM CODE],8)) Not In (73121010)) AND ((Left([COMM CODE],6))
In (732620))) OR (((Left([COMM CODE],4)) In
(7213,7217,7221,7223,7227,7229,7312,7313,7314,7317,8311)));

COMM CODE has 10 digits but my criteria needs to be less digits because they
refer to different groups.

I can't make the "NOT IN" to work. I also tried OR between that criteria
and the next, also <> instead of not in. Every time I go to datasheet and I
still find the 73121010 code!
What is that I 'm doing wrong.

Maybe I could have use COMM CODE without the left formula and use *?

Please help!
Click to expand...
Click to expand...
 
F

FA

Should I use a subquery? I have never use them...

Klatuu said:
You are explicitly asking for those records in the OR side of your criteria.
7312 is listed.
--
Dave Hargis, Microsoft Access MVP


FA said:
Hello,

I have the following SQL:

SELECT ETS.*
FROM ETS
WHERE (((Left([COMM CODE],8)) Not In (73121010)) AND ((Left([COMM CODE],6))
In (732620))) OR (((Left([COMM CODE],4)) In
(7213,7217,7221,7223,7227,7229,7312,7313,7314,7317,8311)));

COMM CODE has 10 digits but my criteria needs to be less digits because they
refer to different groups.

I can't make the "NOT IN" to work. I also tried OR between that criteria
and the next, also <> instead of not in. Every time I go to datasheet and I
still find the 73121010 code!
What is that I 'm doing wrong.

Maybe I could have use COMM CODE without the left formula and use *?

Please help!
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
SELECT ETS.*
FROM ETS
WHERE Left([COMM CODE],6) In (732620) OR (Left([COMM CODE],4) In
(7213,7217,7221,7223,7227,7229,7312,7313,7314,7317,8311) AND Left([COMM
CODE],8) Not In (73121010));

--
Build a little, test a little.


FA said:
I know but how should I ask it to give those in 7312 except 73121010?

Klatuu said:
You are explicitly asking for those records in the OR side of your criteria.
7312 is listed.
--
Dave Hargis, Microsoft Access MVP


FA said:
Hello,

I have the following SQL:

SELECT ETS.*
FROM ETS
WHERE (((Left([COMM CODE],8)) Not In (73121010)) AND ((Left([COMM CODE],6))
In (732620))) OR (((Left([COMM CODE],4)) In
(7213,7217,7221,7223,7227,7229,7312,7313,7314,7317,8311)));

COMM CODE has 10 digits but my criteria needs to be less digits because they
refer to different groups.

I can't make the "NOT IN" to work. I also tried OR between that criteria
and the next, also <> instead of not in. Every time I go to datasheet and I
still find the 73121010 code!
What is that I 'm doing wrong.

Maybe I could have use COMM CODE without the left formula and use *?

Please help!
Click to expand...
Click to expand...
Click to expand...
 
F

FA

Thank you. It works. Was the order the problem?

KARL DEWEY said:
Try this --
SELECT ETS.*
FROM ETS
WHERE Left([COMM CODE],6) In (732620) OR (Left([COMM CODE],4) In
(7213,7217,7221,7223,7227,7229,7312,7313,7314,7317,8311) AND Left([COMM
CODE],8) Not In (73121010));

--
Build a little, test a little.


FA said:
I know but how should I ask it to give those in 7312 except 73121010?

Klatuu said:
You are explicitly asking for those records in the OR side of your criteria.
7312 is listed.
--
Dave Hargis, Microsoft Access MVP


:

Hello,

I have the following SQL:

SELECT ETS.*
FROM ETS
WHERE (((Left([COMM CODE],8)) Not In (73121010)) AND ((Left([COMM CODE],6))
In (732620))) OR (((Left([COMM CODE],4)) In
(7213,7217,7221,7223,7227,7229,7312,7313,7314,7317,8311)));

COMM CODE has 10 digits but my criteria needs to be less digits because they
refer to different groups.

I can't make the "NOT IN" to work. I also tried OR between that criteria
and the next, also <> instead of not in. Every time I go to datasheet and I
still find the 73121010 code!
What is that I 'm doing wrong.

Maybe I could have use COMM CODE without the left formula and use *?

Please help!
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Not the order alone. Your In(.. must be And'd with the excluison as a single
expression --
Something OR Something_Else OR ( In(.. ) AND NOT This)

The parenthesis sets the expression apart as a whole. Access adds lot of
unnecessary parenthesis that can be confusing.

--
Build a little, test a little.


FA said:
Thank you. It works. Was the order the problem?

KARL DEWEY said:
Try this --
SELECT ETS.*
FROM ETS
WHERE Left([COMM CODE],6) In (732620) OR (Left([COMM CODE],4) In
(7213,7217,7221,7223,7227,7229,7312,7313,7314,7317,8311) AND Left([COMM
CODE],8) Not In (73121010));

--
Build a little, test a little.


FA said:
I know but how should I ask it to give those in 7312 except 73121010?

:

You are explicitly asking for those records in the OR side of your criteria.
7312 is listed.
--
Dave Hargis, Microsoft Access MVP


:

Hello,

I have the following SQL:

SELECT ETS.*
FROM ETS
WHERE (((Left([COMM CODE],8)) Not In (73121010)) AND ((Left([COMM CODE],6))
In (732620))) OR (((Left([COMM CODE],4)) In
(7213,7217,7221,7223,7227,7229,7312,7313,7314,7317,8311)));

COMM CODE has 10 digits but my criteria needs to be less digits because they
refer to different groups.

I can't make the "NOT IN" to work. I also tried OR between that criteria
and the next, also <> instead of not in. Every time I go to datasheet and I
still find the 73121010 code!
What is that I 'm doing wrong.

Maybe I could have use COMM CODE without the left formula and use *?

Please help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple condition message box 1
Criteria & <> 4
type mismatch error in macro in excel 0
Update query using Left function 3
Update query using Left function 1
Report based on criteria form 5
VBA Code for converting Type-E UPC to Type-A (barcodes) 1
Using a vlookup to search multiple criteria for one result? 7

Top