Criteria in Query

[GeorgeMar]

I have a query with a field called Subject. In the query
Builder, I enter a function fnSubject() that contains the
subject to filter on, instead of the value. It works well
if there is only one subject, say "A".

The user determines which subject(say "A") and query
filters for "A".

Now, when I have more than one subject, say "A" OR "B"
OR "C", then it doesn't work. If I enter the "A" OR "B"
OR "C" directly into the Query Builder, it works.

If the user has chosen a number of subjects, I create the
text - "A" OR "B" OR "C" which goes into the function and
query doesn't work.

I have tried using single quotes('), double quotes("), no
quotes and nothing works.

Any idea what the syntax should be?

many thanks
george

 

[Marshall Barton]

I have a query with a field called Subject. In the query
Builder, I enter a function fnSubject() that contains the
subject to filter on, instead of the value. It works well
if there is only one subject, say "A".

The user determines which subject(say "A") and query
filters for "A".

Now, when I have more than one subject, say "A" OR "B"
OR "C", then it doesn't work. If I enter the "A" OR "B"
OR "C" directly into the Query Builder, it works.

If the user has chosen a number of subjects, I create the
text - "A" OR "B" OR "C" which goes into the function and
query doesn't work.

What you enter into a criteria cell in the QBE grid is not
the condition the query actually uses, it's just a
convenient shorthand that Access translates into SQL.

When you use "A" in a field's crtieria cell the translated
Where clause is:
[field] = "A"

If you use your function, it's:
[field] = fnSubject()
and when fnSubject() returns "A", the result is the same.

But, if the criteria cell has "A" OR "B" OR "C", the
translation is:
[field] = "A" Or [field] = "B" Or [field] = "C"
but your function doesn't do the translated operation, so
the result of using [field] = fnSubject() is the equivalent
of:
[field] = '"A" Or "B" Or "C"'
which doesn't make any sense.

To use a function in this kind of situation, the function
has to perform the comparisons and return a True or False
value instead of constructing a string. To do this in the
QBE grid, you would add a calculated column like this:
Expr1: fnSubject([field])
with the criteria cell containing just True

 

[Phil Hunt]

If you change your function to set the criteria to [field] in
("A","B","C"), i think that would work.

I have a query with a field called Subject. In the query
Builder, I enter a function fnSubject() that contains the
subject to filter on, instead of the value. It works well
if there is only one subject, say "A".

The user determines which subject(say "A") and query
filters for "A".

Now, when I have more than one subject, say "A" OR "B"
OR "C", then it doesn't work. If I enter the "A" OR "B"
OR "C" directly into the Query Builder, it works.

If the user has chosen a number of subjects, I create the
text - "A" OR "B" OR "C" which goes into the function and
query doesn't work.

What you enter into a criteria cell in the QBE grid is not
the condition the query actually uses, it's just a
convenient shorthand that Access translates into SQL.

When you use "A" in a field's crtieria cell the translated
Where clause is:
[field] = "A"

If you use your function, it's:
[field] = fnSubject()
and when fnSubject() returns "A", the result is the same.

But, if the criteria cell has "A" OR "B" OR "C", the
translation is:
[field] = "A" Or [field] = "B" Or [field] = "C"
but your function doesn't do the translated operation, so
the result of using [field] = fnSubject() is the equivalent
of:
[field] = '"A" Or "B" Or "C"'
which doesn't make any sense.

To use a function in this kind of situation, the function
has to perform the comparisons and return a True or False
value instead of constructing a string. To do this in the
QBE grid, you would add a calculated column like this:
Expr1: fnSubject([field])
with the criteria cell containing just True

 

[GeorgeMar]

Thank you all.

Phil, I don't quite sure I understand what the text line
would look like.

regards
george

-----Original Message-----
If you change your function to set the criteria to [field] in
("A","B","C"), i think that would work.

I have a query with a field called Subject. In the query
Builder, I enter a function fnSubject() that contains the
subject to filter on, instead of the value. It works well
if there is only one subject, say "A".

The user determines which subject(say "A") and query
filters for "A".

Now, when I have more than one subject, say "A" OR "B"
OR "C", then it doesn't work. If I enter the "A" OR "B"
OR "C" directly into the Query Builder, it works.

If the user has chosen a number of subjects, I create the
text - "A" OR "B" OR "C" which goes into the function and
query doesn't work.

What you enter into a criteria cell in the QBE grid is not
the condition the query actually uses, it's just a
convenient shorthand that Access translates into SQL.

When you use "A" in a field's crtieria cell the translated
Where clause is:
[field] = "A"

If you use your function, it's:
[field] = fnSubject()
and when fnSubject() returns "A", the result is the same.

But, if the criteria cell has "A" OR "B" OR "C", the
translation is:
[field] = "A" Or [field] = "B" Or [field] = "C"
but your function doesn't do the translated operation, so
the result of using [field] = fnSubject() is the equivalent
of:
[field] = '"A" Or "B" Or "C"'
which doesn't make any sense.

To use a function in this kind of situation, the function
has to perform the comparisons and return a True or False
value instead of constructing a string. To do this in the
QBE grid, you would add a calculated column like this:
Expr1: fnSubject([field])
with the criteria cell containing just True

.

 

[GeorgeMar]

Phil

I've worked out what you meant. But the problem is still
the same. I'll keep trying.

george

-----Original Message-----
If you change your function to set the criteria to [field] in
("A","B","C"), i think that would work.

I have a query with a field called Subject. In the query
Builder, I enter a function fnSubject() that contains the
subject to filter on, instead of the value. It works well
if there is only one subject, say "A".

The user determines which subject(say "A") and query
filters for "A".

Now, when I have more than one subject, say "A" OR "B"
OR "C", then it doesn't work. If I enter the "A" OR "B"
OR "C" directly into the Query Builder, it works.

If the user has chosen a number of subjects, I create the
text - "A" OR "B" OR "C" which goes into the function and
query doesn't work.

What you enter into a criteria cell in the QBE grid is not
the condition the query actually uses, it's just a
convenient shorthand that Access translates into SQL.

When you use "A" in a field's crtieria cell the translated
Where clause is:
[field] = "A"

If you use your function, it's:
[field] = fnSubject()
and when fnSubject() returns "A", the result is the same.

But, if the criteria cell has "A" OR "B" OR "C", the
translation is:
[field] = "A" Or [field] = "B" Or [field] = "C"
but your function doesn't do the translated operation, so
the result of using [field] = fnSubject() is the equivalent
of:
[field] = '"A" Or "B" Or "C"'
which doesn't make any sense.

To use a function in this kind of situation, the function
has to perform the comparisons and return a True or False
value instead of constructing a string. To do this in the
QBE grid, you would add a calculated column like this:
Expr1: fnSubject([field])
with the criteria cell containing just True

.

 

[Marshall Barton]

What I said before about using your function also applies to
the IN operator. You can not use any mechanism to supply
both the operators (=, OR, IN, etc) and their operands ("A",
"B", etc) to a criteria. The operators (and any associated
syntax) must be part of the Where clause before the query is
executed.

If you're going to use a function as part of a criteria,
then the function must return a single operand value, or it
must perform the entire operation and return a True/False
value.

The only other way to do this kind of variable filtering is
to construct the SQL in code.

You're just beating your head against a brick wall if you
keep trying to go down the path you seem to be stuck on.
--
Marsh
MVP [MS Access]

Phil

I've worked out what you meant. But the problem is still
the same. I'll keep trying.

george

-----Original Message-----
If you change your function to set the criteria to [field] in
("A","B","C"), i think that would work.

GeorgeMar wrote:

I have a query with a field called Subject. In the query
Builder, I enter a function fnSubject() that contains the
subject to filter on, instead of the value. It works well
if there is only one subject, say "A".

The user determines which subject(say "A") and query
filters for "A".

Now, when I have more than one subject, say "A" OR "B"
OR "C", then it doesn't work. If I enter the "A" OR "B"
OR "C" directly into the Query Builder, it works.

If the user has chosen a number of subjects, I create the
text - "A" OR "B" OR "C" which goes into the function and
query doesn't work.

What you enter into a criteria cell in the QBE grid is not
the condition the query actually uses, it's just a
convenient shorthand that Access translates into SQL.

When you use "A" in a field's crtieria cell the translated
Where clause is:
[field] = "A"

If you use your function, it's:
[field] = fnSubject()
and when fnSubject() returns "A", the result is the same.

But, if the criteria cell has "A" OR "B" OR "C", the
translation is:
[field] = "A" Or [field] = "B" Or [field] = "C"
but your function doesn't do the translated operation, so
the result of using [field] = fnSubject() is the equivalent
of:
[field] = '"A" Or "B" Or "C"'
which doesn't make any sense.

To use a function in this kind of situation, the function
has to perform the comparisons and return a True or False
value instead of constructing a string. To do this in the
QBE grid, you would add a calculated column like this:
Expr1: fnSubject([field])
with the criteria cell containing just True

 

[GeorgeMar]

Marsh

I agree with you. I will construct a temporary table
using SQL as I had before.

Thanks for your help.

george

-----Original Message-----
What I said before about using your function also applies to
the IN operator. You can not use any mechanism to supply
both the operators (=, OR, IN, etc) and their operands ("A",
"B", etc) to a criteria. The operators (and any associated
syntax) must be part of the Where clause before the query is
executed.

If you're going to use a function as part of a criteria,
then the function must return a single operand value, or it
must perform the entire operation and return a True/False
value.

The only other way to do this kind of variable filtering is
to construct the SQL in code.

You're just beating your head against a brick wall if you
keep trying to go down the path you seem to be stuck on.
--
Marsh
MVP [MS Access]

Phil

I've worked out what you meant. But the problem is still
the same. I'll keep trying.

george

-----Original Message-----
If you change your function to set the criteria to [field] in
("A","B","C"), i think that would work.

GeorgeMar wrote:

I have a query with a field called Subject. In the query
Builder, I enter a function fnSubject() that

contains
the

subject to filter on, instead of the value. It

works
well

if there is only one subject, say "A".

The user determines which subject(say "A") and query
filters for "A".

Now, when I have more than one subject, say "A" OR "B"
OR "C", then it doesn't work. If I enter the "A" OR "B"
OR "C" directly into the Query Builder, it works.

If the user has chosen a number of subjects, I

create
the

text - "A" OR "B" OR "C" which goes into the

function
and

query doesn't work.


:
What you enter into a criteria cell in the QBE grid

is
not

the condition the query actually uses, it's just a
convenient shorthand that Access translates into SQL.

When you use "A" in a field's crtieria cell the translated
Where clause is:
[field] = "A"

If you use your function, it's:
[field] = fnSubject()
and when fnSubject() returns "A", the result is the same.

But, if the criteria cell has "A" OR "B" OR "C", the
translation is:
[field] = "A" Or [field] = "B" Or [field] = "C"
but your function doesn't do the translated

operation,
so

the result of using [field] = fnSubject() is the equivalent
of:
[field] = '"A" Or "B" Or "C"'
which doesn't make any sense.

To use a function in this kind of situation, the function
has to perform the comparisons and return a True or False
value instead of constructing a string. To do this

in
the

QBE grid, you would add a calculated column like this:
Expr1: fnSubject([field])
with the criteria cell containing just True

.

 

[Marshall Barton]

George, you can use a function as I said before, but it will
suffer a potentially tremendous performance hit (because the
query will not be able to take advantage of indexes).

If speed is an issue, then constructing the appropriate
query in code is a good bet and sometimes multiple queries
with an intermediate temporary table can improve the overall
spped. There are some situation with complex reports where
making a table as the final record source can be of
significant benefit, but normally creating a temporary table
is a net loss or just a break even proposition.

If you do use a temp table, be sure to give serious
consideration to putting the temp table in a temporary mdb.
One way of managing that is described here:
http://www.granite.ab.ca/access/temptables.htm
--
Marsh
MVP [MS Access]

Marsh

I agree with you. I will construct a temporary table
using SQL as I had before.

Thanks for your help.

george

-----Original Message-----
What I said before about using your function also applies to
the IN operator. You can not use any mechanism to supply
both the operators (=, OR, IN, etc) and their operands ("A",
"B", etc) to a criteria. The operators (and any associated
syntax) must be part of the Where clause before the query is
executed.

If you're going to use a function as part of a criteria,
then the function must return a single operand value, or it
must perform the entire operation and return a True/False
value.

The only other way to do this kind of variable filtering is
to construct the SQL in code.

You're just beating your head against a brick wall if you
keep trying to go down the path you seem to be stuck on.
--
Marsh
MVP [MS Access]

Phil

I've worked out what you meant. But the problem is still
the same. I'll keep trying.

george

-----Original Message-----
If you change your function to set the criteria to
[field] in
("A","B","C"), i think that would work.

GeorgeMar wrote:

I have a query with a field called Subject. In the
query
Builder, I enter a function fnSubject() that contains
the
subject to filter on, instead of the value. It works
well
if there is only one subject, say "A".

The user determines which subject(say "A") and query
filters for "A".

Now, when I have more than one subject, say "A" OR "B"
OR "C", then it doesn't work. If I enter the "A"
OR "B"
OR "C" directly into the Query Builder, it works.

If the user has chosen a number of subjects, I create
the
text - "A" OR "B" OR "C" which goes into the function
and
query doesn't work.


:
What you enter into a criteria cell in the QBE grid is
not
the condition the query actually uses, it's just a
convenient shorthand that Access translates into SQL.

When you use "A" in a field's crtieria cell the
translated
Where clause is:
[field] = "A"

If you use your function, it's:
[field] = fnSubject()
and when fnSubject() returns "A", the result is the
same.

But, if the criteria cell has "A" OR "B" OR "C", the
translation is:
[field] = "A" Or [field] = "B" Or [field] = "C"
but your function doesn't do the translated operation,
so
the result of using [field] = fnSubject() is the
equivalent
of:
[field] = '"A" Or "B" Or "C"'
which doesn't make any sense.

To use a function in this kind of situation, the
function
has to perform the comparisons and return a True or
False
value instead of constructing a string. To do this in
the
QBE grid, you would add a calculated column like this:
Expr1: fnSubject([field])
with the criteria cell containing just True

.