Restricting records from multiple fields

[mikew71]

Hi,

I'm building a query that needs to restrict on several fields, but only where the records match this exact combination of variables.

I've done this in the past by building individual queries for each element of the extraction to avoid excluding more records than I want but would prefer to do so in one query.

the logic is as follows;

select [Dataset].[filed1],[Dataset].[field2])
from [Dataset]
where ([field1])<>0 and where ([field2]) <> "Definition1" or "definition2"

any ideas?

 

[r937]

you can have only one WHERE keyword

where [field1] <> 0
and [field2] <> "Definition1"
and [field2] <> "definition2"


r937.com | rudy.ca

 

[lespaul]

and you can use IN to replace a String of ORs

Correct Syntax:

select [Dataset].[field1],[Dataset].[field2]
from [Dataset]
where [field1]<>0 and ([field2] <> "Definition1" or [field2] <> "definition2")

Alternate Syntax:
select [Dataset].[field1],[Dataset].[field2]
from [Dataset]
where ([field1])<>0 and [field2] NOT IN ("Definition1", "definition2")


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[r937]

leslie your "Correct Syntax" is sadly incorrect

field2 will either be not equal to "Definition1" or it will be not equal to "definition2"

you meant to use AND instead of OR



r937.com | rudy.ca

 

[Golom]

'fraid I must agree with Rudy ... sort of.

Regardless of the value in [field2] it will always be not equal to at least one of the values "Definition1" or "definition2".

The syntax is correct ... it's the semantics that's flawed.

 

[lespaul]

my bad, too early in the morning!!!

Still the NOT IN will work!


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[mikew71]

This was my previous understanding. This its not possible to conditionally restrict a combination of more than 1 field in a query.

I've been working quite extensivly with BO where this is possible and was hoping that my Access knowledge was simply not up to scratch.

Thanks

 

[PHV]

This its not possible to conditionally restrict a combination of more than 1 field in a query
???