Question about criteria

[Jeff Hunt]

I have a query that has half of the results from one category and the other
half for many different categories. I’m trying to come up with a query to
base a report on, but I would like to filter either for just the one category
or for everything but that category. Still not sure how I will implement the
difference, so for now my test query just has a prompt in the criteria. What
I would like to get it to do is something like this:

IIf([y for TIM]="y", "TIM", Not Like "TIM")

This criteria works fine when you enter “yâ€, but it does not work for
anything else (no records returned). Is this type of criteria possible in
the query builder (If condition A then B else NOT B)?

Thanks.

…jeff…

 

[John Spencer]

SQL would look something like

SELECT *
FROM SomeTable
WHERE (somefield = "Tim" and [Y for TIM] = "y")
Or (Somefield <> "Tim" and [Y for TIM] <> "y")

In the query grid (design view), you could enter:

Field: YourFieldName
Criteria(Line1): "Tim" and [Y for TIM] = "y"
Criteria(Line2):<> "Tim" and [Y for TIM] <> "y"

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff Hunt]

That worked perfectly. Thank you!

SQL would look something like

SELECT *
FROM SomeTable
WHERE (somefield = "Tim" and [Y for TIM] = "y")
Or (Somefield <> "Tim" and [Y for TIM] <> "y")

In the query grid (design view), you could enter:

Field: YourFieldName
Criteria(Line1): "Tim" and [Y for TIM] = "y"
Criteria(Line2):<> "Tim" and [Y for TIM] <> "y"

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a query that has half of the results from one category and the other
half for many different categories. I’m trying to come up with a query to
base a report on, but I would like to filter either for just the one category
or for everything but that category. Still not sure how I will implement the
difference, so for now my test query just has a prompt in the criteria. What
I would like to get it to do is something like this:

IIf([y for TIM]="y", "TIM", Not Like "TIM")

This criteria works fine when you enter “yâ€, but it does not work for
anything else (no records returned). Is this type of criteria possible in
the query builder (If condition A then B else NOT B)?

Thanks.

…jeff…