HELP! AND / OR criteria

[Guest]

I have a complicated query that is driving me nuts.

Is there any way to keep AND statements together within a single field in
query design? Access is automatically splitting the AND statements into more
than 1 field. Even going into the SQL statement, it is still splitting the
AND statements. I want the query to remain how I design it:
Field 1: ((Like [1] AND B1=X) OR (Not Like [1] AND B1=Y))
Field 2: ((Like [2] AND B2=X) OR (Not Like [2] AND B2=Y))
In 10 different fields total. In design view, each AND statement is on a
different criteria row, to account for the OR

The problem is, Access splits the AND statments for each line out, giving
them their own calculated field, and the query becomes:
(Like [1] AND Like [2] AND B1=X AND B2=X) OR (Not Like [1] AND Not Like [2]
AND B1=Y AND B2=Y)

Which is a totally different criteria from what I designed, and if any 1 of
B1 or B2, etc... = Y at all, then it fails, returning nothing, since it is
looking for Like [1] and Not Like [1] at the same time. It would only return
information filtering out what I want, if I make every B1, B2, etc... = Y.

Going into the SQL statement and manually arranging the AND / OR statements
the way I want, keeps the statements that way IN SQL VIEW, but still splits
them in design view, and treats it exactly like splitting it under run-time.

 

[Michel Walsh]

in SQL view, try:


WHERE iif( field1 LIKE [1], B1=X, B1=Y) AND iif( field2 LIKE [2], B2=X,
B2=Y )


Note that this implies that parameter [1] has some wildcard, else, consider
using =, not LIKE.


I am not sure if you desire an AND or an OR between the iif-s. With AND, you
keep a record if B1=X when field1 LIKE [1] and if, for the same record,
B2=X when field2 LIKE [2]. (There are other cases where you keep the record,
that is just but ONE example where you keep it). With an OR, the record is
returned if at least one iif returns true: field1 like [1] and B1<>X and
field2 like [2] and B2=X would be kept, if you use a OR rather than a AND.


Vanderghast, Access MVP

 

[Guest]

Thank you for the response.

Originally I had tried using IIF statements, however I was having a large
amount of trouble with them, and upon searching the forums here, I found a
comment or 2 that IIF statements only return values, and can not return
operators. You suggestion would in-fact work as it is, however you have the
criteria reversed:

To return what I need, it would have to be:
WHERE IIF(B1=X, Field1 Like [1],Field1 Not Like [1]) AND IIF(B2=X, Field2
Like [2],Field2 Not Like [2])

Which does not work, since the IIF statement is returning operators.

For a bit of clarification, B1 and B2 represent combo boxes on a form, from
which the user selects 'Show All' or 'Exclude' to specify filtering criteria,
to FilterBy or FilterExcluding, and the [1], [2] respresent values in a
second combo box that displays possible values in the corresponding fields.
(My naming all works, I just changed it for this due to the length of the
control reference strings.)

I can get everything to work with all possible 'B's on 'Y', or all on 'X'
but not a mix of them, 'filtering out' a value in one field and 'filtering
on' a value in another

And yes, the default value for the combo boxes is a "*" to return all
values/records, so I have to use Like rather than '='

Thanks again for any input you may have.

(Feel free to keep asking, challenging, and pushing me, I have no issue with
expanding my knowledge limits)

 

[Michel Walsh]

that should work, I mean, as far as the syntax is implied:


WHERE IIF(B1=X, Field1 Like [1], Field1 Not Like [1])
AND IIF(B2=X, Field2 Like [2], Field2 Not Like [2])


would run without error, since each argument of the iif-s is evaluable:
Field1 LIKE [1] , the second argument, is evaluable, not an incomplete
expression. Same for Field1 NOT LIKE [1].


What is NOT acceptable is something like:

Field1 IIF(B1=X, Like [1], Not Like [1])

because there, the second argument, neither the third, is evaluable.


Vanderghast, Access MVP

Thank you for the response.

Originally I had tried using IIF statements, however I was having a large
amount of trouble with them, and upon searching the forums here, I found a
comment or 2 that IIF statements only return values, and can not return
operators. You suggestion would in-fact work as it is, however you have
the
criteria reversed:

To return what I need, it would have to be:
WHERE IIF(B1=X, Field1 Like [1],Field1 Not Like [1]) AND IIF(B2=X, Field2
Like [2],Field2 Not Like [2])

Which does not work, since the IIF statement is returning operators.

For a bit of clarification, B1 and B2 represent combo boxes on a form,
from
which the user selects 'Show All' or 'Exclude' to specify filtering
criteria,
to FilterBy or FilterExcluding, and the [1], [2] respresent values in a
second combo box that displays possible values in the corresponding
fields.
(My naming all works, I just changed it for this due to the length of the
control reference strings.)

I can get everything to work with all possible 'B's on 'Y', or all on 'X'
but not a mix of them, 'filtering out' a value in one field and 'filtering
on' a value in another

And yes, the default value for the combo boxes is a "*" to return all
values/records, so I have to use Like rather than '='

Thanks again for any input you may have.

(Feel free to keep asking, challenging, and pushing me, I have no issue
with
expanding my knowledge limits)

in SQL view, try:


WHERE iif( field1 LIKE [1], B1=X, B1=Y) AND iif( field2 LIKE [2],
B2=X,
B2=Y )


Note that this implies that parameter [1] has some wildcard, else,
consider
using =, not LIKE.


I am not sure if you desire an AND or an OR between the iif-s. With AND,
you
keep a record if B1=X when field1 LIKE [1] and if, for the same record,
B2=X when field2 LIKE [2]. (There are other cases where you keep the
record,
that is just but ONE example where you keep it). With an OR, the record
is
returned if at least one iif returns true: field1 like [1] and B1<>X
and
field2 like [2] and B2=X would be kept, if you use a OR rather than a
AND.


Vanderghast, Access MVP

 

[Guest]

Ah! Understood. This was probably my issue before then. I will try this and
let you know.

Thanks again for the assistance and response.