Problems with the Criteria field

[John F.]

This concerns Access 2007.

I have two columns of the same data, this being a sports fixture list. In a
Query, if I say "NOT("Deckers")", to both columns, this will remove 'Deckers'
from both columns and the teams that they are playing in their Home & Away
games. But what I want is the opposite, I want 'Deckers' (and their
opponents) to remain there and the other teams to be filtered out.

There are other solutions, mainly coding, but I thought someone might have
any easy boolean answer.

In the meantime, I did write a coding solution, passing back from a function
a value of '999' (if 'Deckers' are the Home or Away team) and '0' otherwise -
I can see these values displayed. However, in the Criteria Field if I set
'>0', then I don't see any display at all. I did expect to see the Home/Away
matches for Deckers, i.e. Even the '999' display-values have been filtered
out. Any ideas what I am doing wrong?

From my point of view the Criteria filter field does not seem to be working.

I could forward the database, if anybody would like a look and advise me.

 

[John W. Vinson]

This concerns Access 2007.

I have two columns of the same data, this being a sports fixture list. In a
Query, if I say "NOT("Deckers")", to both columns, this will remove 'Deckers'
from both columns and the teams that they are playing in their Home & Away
games. But what I want is the opposite, I want 'Deckers' (and their
opponents) to remain there and the other teams to be filtered out.

There are other solutions, mainly coding, but I thought someone might have
any easy boolean answer.

In the meantime, I did write a coding solution, passing back from a function
a value of '999' (if 'Deckers' are the Home or Away team) and '0' otherwise -
I can see these values displayed. However, in the Criteria Field if I set
'>0', then I don't see any display at all. I did expect to see the Home/Away
matches for Deckers, i.e. Even the '999' display-values have been filtered
out. Any ideas what I am doing wrong?

From my point of view the Criteria filter field does not seem to be working.

I could forward the database, if anybody would like a look and advise me.

If you want to see the record if Deckers is either the home team or the away
team, you need to put

= 'Deckers'

on the criteria under both columns - on *two different rows* of the criteria
grid. This will use OR logic, to retrieve the record if Home = 'Deckers', *or*
if Away = 'Deckers'.

If there are other criteria you'll need to either duplicate them on both
criteria lines, or go into SQL view and use a criterion including a
parenthesized expression such as

([Home] = 'Deckers' OR [Away] = 'Deckers')

What might help rather than sending someone the whole database is to post a
description of your table structure (with the relevant fields), and the SQL
view (View... SQL on the menu) of the query; indicate what records your query
is retrieving and what you would like it to retrieve.

 

[John F.]

Thanks for your efforts John, I have now solved it.

Yes I was missing something rather obvious, such as using the 'OR' row in
the criteria field. I did think I could get away without using that, not
being very familar with it, by setting up boolean statements elsewhere, but
they didn't work. (I still don't know why the coding solution off '999'
didn't work.)
--
John F.

This concerns Access 2007.

I have two columns of the same data, this being a sports fixture list. In a
Query, if I say "NOT("Deckers")", to both columns, this will remove 'Deckers'
from both columns and the teams that they are playing in their Home & Away
games. But what I want is the opposite, I want 'Deckers' (and their
opponents) to remain there and the other teams to be filtered out.

There are other solutions, mainly coding, but I thought someone might have
any easy boolean answer.

In the meantime, I did write a coding solution, passing back from a function
a value of '999' (if 'Deckers' are the Home or Away team) and '0' otherwise -
I can see these values displayed. However, in the Criteria Field if I set
'>0', then I don't see any display at all. I did expect to see the Home/Away
matches for Deckers, i.e. Even the '999' display-values have been filtered
out. Any ideas what I am doing wrong?

From my point of view the Criteria filter field does not seem to be working.

I could forward the database, if anybody would like a look and advise me.

If you want to see the record if Deckers is either the home team or the away
team, you need to put

= 'Deckers'

on the criteria under both columns - on *two different rows* of the criteria
grid. This will use OR logic, to retrieve the record if Home = 'Deckers', *or*
if Away = 'Deckers'.

If there are other criteria you'll need to either duplicate them on both
criteria lines, or go into SQL view and use a criterion including a
parenthesized expression such as

([Home] = 'Deckers' OR [Away] = 'Deckers')

What might help rather than sending someone the whole database is to post a
description of your table structure (with the relevant fields), and the SQL
view (View... SQL on the menu) of the query; indicate what records your query
is retrieving and what you would like it to retrieve.