Can I combine BETWEEN and NOT in the same query criteria?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi -- I'm a new user to Access 2003 and struggling with a query criteria that
is probably simple but I'm missing some aspect of it! :) I am asking for a
group of numbers (SIC codes actually) between 2674 and 2679 but I want to
exclude 2677 and 2678. I wrote my criteria as
Between '2674' and '2679' not "2677" not "2678"
I get results but the numbers I want excluded are still included. I've
played with different ways of expressing this based on the help screens but
am not doing something quite right.

Can anyone help this Access newbie? Many thanks in advance!

Sue
 
Thank you Karl. I copied your exact words and entered them, then ran the
query. It comes back with no results. Access altered your criteria when I
tabbed to the next criteria field in Design View as follows:

BETWEEN '[2674]' AND '[2679]' and it took the NOT part of it and threw it
into what would be criteria for another 2 fields from my table. That isn't
what I wanted and I have no idea why it is doing this.

Suggestions?
 
Is your field text or number?

Post your SQL statement by opening the query in design view, click on menu
VIEW - SQL View. Highlight, copy and paste in a post.

Sue said:
Thank you Karl. I copied your exact words and entered them, then ran the
query. It comes back with no results. Access altered your criteria when I
tabbed to the next criteria field in Design View as follows:

BETWEEN '[2674]' AND '[2679]' and it took the NOT part of it and threw it
into what would be criteria for another 2 fields from my table. That isn't
what I wanted and I have no idea why it is doing this.

Suggestions?

KARL DEWEY said:
I assume the field is text.
Between "2674" and "2679" And <> "2677" And <> "2678"
Click to expand...
Click to expand...
 
How about:
"2679" or Between "2674" and "2676"

This is conceptual only, and may vary depending on the datatype.

KARL DEWEY said:
Is your field text or number?

Post your SQL statement by opening the query in design view, click on menu
VIEW - SQL View. Highlight, copy and paste in a post.

Sue said:
Thank you Karl. I copied your exact words and entered them, then ran the
query. It comes back with no results. Access altered your criteria when
I
tabbed to the next criteria field in Design View as follows:

BETWEEN '[2674]' AND '[2679]' and it took the NOT part of it and threw it
into what would be criteria for another 2 fields from my table. That
isn't
what I wanted and I have no idea why it is doing this.

Suggestions?

KARL DEWEY said:
I assume the field is text.
Between "2674" and "2679" And <> "2677" And <> "2678"


:

Hi -- I'm a new user to Access 2003 and struggling with a query
criteria that
is probably simple but I'm missing some aspect of it! :) I am asking
for a
group of numbers (SIC codes actually) between 2674 and 2679 but I
want to
exclude 2677 and 2678. I wrote my criteria as
Between '2674' and '2679' not "2677" not "2678"
I get results but the numbers I want excluded are still included.
I've
played with different ways of expressing this based on the help
screens but
am not doing something quite right.

Can anyone help this Access newbie? Many thanks in advance!

Sue
Click to expand...
Click to expand...
Click to expand...
 
I have it figured out -- I removed the brackets in your criteria and used ' '
around the data I'm searching for. And, my response back to you was
incorrect -- Access added the same field twice in my columns in design view
but isn't outputting them -- each field had the <> excluded numbers that I
was trying to eliminate. So that's OK -- it is working now. Do you have any
suggestions for a book or website that would help us learn criteria
expressions?

Many many thanks!

KARL DEWEY said:
Is your field text or number?

Post your SQL statement by opening the query in design view, click on menu
VIEW - SQL View. Highlight, copy and paste in a post.

Sue said:
Thank you Karl. I copied your exact words and entered them, then ran the
query. It comes back with no results. Access altered your criteria when I
tabbed to the next criteria field in Design View as follows:

BETWEEN '[2674]' AND '[2679]' and it took the NOT part of it and threw it
into what would be criteria for another 2 fields from my table. That isn't
what I wanted and I have no idea why it is doing this.

Suggestions?

KARL DEWEY said:
I assume the field is text.
Between "2674" and "2679" And <> "2677" And <> "2678"


:

Hi -- I'm a new user to Access 2003 and struggling with a query criteria that
is probably simple but I'm missing some aspect of it! :) I am asking for a
group of numbers (SIC codes actually) between 2674 and 2679 but I want to
exclude 2677 and 2678. I wrote my criteria as
Between '2674' and '2679' not "2677" not "2678"
I get results but the numbers I want excluded are still included. I've
played with different ways of expressing this based on the help screens but
am not doing something quite right.

Can anyone help this Access newbie? Many thanks in advance!

Sue
Click to expand...
Click to expand...
Click to expand...
 
Back
Top