multiple OR criteria

  • Thread starter Thread starter jhugo54
  • Start date Start date
J

jhugo54

In SQL view of the query, in the WHERE clause, the section looks like:
((PROJECTS.DeptNo)="44001" Or (PROJECTS.DeptNo)="TAC"))

So, in the query grid, I need to pass the following "Or'ed Strings"
"44001" Or "TAC"
as the [DeptNo] Criteria.

But I'd like to have a function to do so because I'll be building hundreds
of queries based on similar information. My problem is that my attempts only
produce:
""44001" Or "TAC""
which has outer quotes, which of course results in zero rows found.

Is there a method that works? So far I've played with dlookup, in(), custom
functions, etc, but no luck.

Thx.
 
You can place the values inside a small table and make an inner join.

You can use a criteria. In the first line, make the computed expression:

"," & param & ","

and in the criteria line, use:
LIKE "*[, ]" & DeptNo & "[, ]*"


To run the query, supply the param value suach as:

44001, TAC, Hello

and the records having deptNo values equal to 44001, TAC or to Hello
will be picked up

That assumes there is no space inside a value for deptNo, ie, not like "CA
12345" since spaces (and coma) are considered delimiters for the token you
seek, with the actual syntax.



Vanderghast, Access MVP
 
I'm don't know how to go about what you're descibing below but here's the
simplified SQL view of the query:
SELECT PROJECTS.PN, PROJECTS.CategoryID, PROJECTS.RequestDate, PROJECTS.DeptNo
FROM PROJECTS
WHERE (((PROJECTS.DeptNo)="44001" Or (PROJECTS.DeptNo)="TAC"));

I'm trying to pass in "44001" Or "TAC" as if in query design view...

Thx.

vanderghast said:
You can place the values inside a small table and make an inner join.

You can use a criteria. In the first line, make the computed expression:

"," & param & ","

and in the criteria line, use:
LIKE "*[, ]" & DeptNo & "[, ]*"


To run the query, supply the param value suach as:

44001, TAC, Hello

and the records having deptNo values equal to 44001, TAC or to Hello
will be picked up

That assumes there is no space inside a value for deptNo, ie, not like "CA
12345" since spaces (and coma) are considered delimiters for the token you
seek, with the actual syntax.



Vanderghast, Access MVP


jhugo54 said:
In SQL view of the query, in the WHERE clause, the section looks like:
((PROJECTS.DeptNo)="44001" Or (PROJECTS.DeptNo)="TAC"))

So, in the query grid, I need to pass the following "Or'ed Strings"
"44001" Or "TAC"
as the [DeptNo] Criteria.

But I'd like to have a function to do so because I'll be building hundreds
of queries based on similar information. My problem is that my attempts
only
produce:
""44001" Or "TAC""
which has outer quotes, which of course results in zero rows found.

Is there a method that works? So far I've played with dlookup, in(),
custom
functions, etc, but no luck.

Thx.
Click to expand...
Click to expand...
 
SELECT PROJECTS.PN, PROJECTS.CategoryID, PROJECTS.RequestDate,
PROJECTS.DeptNo
FROM PROJECTS
WHERE ("," & "44001, TAC" & "," ) LIKE ("*[, ]" & DeptNo & "[, ]*" )



where "44001, TAC" can be replaced by a parameter name, if you wish to
have it 'variable'.



Vanderghast, Access MVP



jhugo54 said:
I'm don't know how to go about what you're descibing below but here's the
simplified SQL view of the query:
SELECT PROJECTS.PN, PROJECTS.CategoryID, PROJECTS.RequestDate,
PROJECTS.DeptNo
FROM PROJECTS
WHERE (((PROJECTS.DeptNo)="44001" Or (PROJECTS.DeptNo)="TAC"));

I'm trying to pass in "44001" Or "TAC" as if in query design view...

Thx.

vanderghast said:
You can place the values inside a small table and make an inner join.

You can use a criteria. In the first line, make the computed expression:

"," & param & ","

and in the criteria line, use:
LIKE "*[, ]" & DeptNo & "[, ]*"


To run the query, supply the param value suach as:

44001, TAC, Hello

and the records having deptNo values equal to 44001, TAC or to Hello
will be picked up

That assumes there is no space inside a value for deptNo, ie, not like
"CA
12345" since spaces (and coma) are considered delimiters for the token
you
seek, with the actual syntax.



Vanderghast, Access MVP


jhugo54 said:
In SQL view of the query, in the WHERE clause, the section looks like:
((PROJECTS.DeptNo)="44001" Or (PROJECTS.DeptNo)="TAC"))

So, in the query grid, I need to pass the following "Or'ed Strings"
"44001" Or "TAC"
as the [DeptNo] Criteria.

But I'd like to have a function to do so because I'll be building
hundreds
of queries based on similar information. My problem is that my
attempts
only
produce:
""44001" Or "TAC""
which has outer quotes, which of course results in zero rows found.

Is there a method that works? So far I've played with dlookup, in(),
custom
functions, etc, but no luck.

Thx.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top