Function to supply the criteria for my IN Clause

[Phil Latio]

Probably not the best way to do this, but I'd appreciate it if someone
could tell me why the following DOESN'T work;

I have a function (code below) which returns a string based upon a
supplied integer value.
I have a SELECT query (SQL below) which I want to use the function
within an IN clause.

Oddly enough, I don't want a solution, just why it doesn't work would
do me fine!

Many thanks

Phil

Public Function fInClause(intVal As Integer) As String
Dim strVal As String
Dim strQ As String

strQ = Chr(34)

Select Case intVal
Case 1
strVal = strQ & "A, B, C" & strQ
Case 2
strVal = strQ & "Z, F" & strQ
Case 3
strVal = strQ & "Q, T" & strQ
Case 4
strVal = strQ & "D, R, V" & strQ
End Select
fInClause = strVal
End Function
SELECT tblOp.OpID, tblOp.OpSkill
FROM tblOp
WHERE (((tblOp.OpID) In (finclause(1))));

[CDMAPoster]

Phil Latio wrote:

Probably not the best way to do this, but I'd appreciate it if someone
could tell me why the following DOESN'T work;

I have a function (code below) which returns a string based upon a
supplied integer value.
I have a SELECT query (SQL below) which I want to use the function
within an IN clause.

Oddly enough, I don't want a solution, just why it doesn't work would
do me fine!

Many thanks

Phil

Public Function fInClause(intVal As Integer) As String
Dim strVal As String
Dim strQ As String

strQ = Chr(34)

Select Case intVal
Case 1
strVal = strQ & "A, B, C" & strQ
Case 2
strVal = strQ & "Z, F" & strQ
Case 3
strVal = strQ & "Q, T" & strQ
Case 4
strVal = strQ & "D, R, V" & strQ
End Select
fInClause = strVal
End Function
SELECT tblOp.OpID, tblOp.OpSkill
FROM tblOp
WHERE (((tblOp.OpID) In (finclause(1))));

It works great. You just don't have any OpID values that equal the
string "A, B, C" :-). It looks like you need strQ around each letter.
For example,

Case 1
strVal = strQ & "A" & strQ & ", " & strQ & "B" & strQ & ", " & strQ &
"C" & strQ

so that strVal = "A", "B", "C"

James A. Fortune
CD********@FortuneJames.com

In the early 90's I was looking to see if any automotive, motorcycle or
heavy machinery companies were interested in the patent of which I was
co-inventor. I tried IRC Relay Chat with a group named something like
#Nippon. It was about 4 a.m. Japanese Industry executives were using
the chat channel to hold business meetings. A soon as I logged in, all
the login names (about 12) logged off like prairie dogs disappearing.
I though that was interesting so I stayed logged in and waited for a
few minutes. A man from Toronto logged in and started chatting with me
in English. He suddenly realized that I might not be Japanese so he
asked me, "Do you believe that Japan should be the number one economic
power on the planet?" I explained that I wasn't Japanese and that I
was simply there to tell them that I was in the midst of patenting a
new kind of energy recovery transmission.

[ChasW]

It works great. You just don't have any OpID values that equal the
string "A, B, C" :-). It looks like you need strQ around each letter.
For example,

Case 1
strVal = strQ & "A" & strQ & ", " & strQ & "B" & strQ & ", " & strQ &
"C" & strQ

so that strVal = "A", "B", "C"

James A. Fortune
CD********@FortuneJames.com

I do not see how this can work "great" even with your correction.

I am trying to do the same as the original poster, but am finding that
a function with a type String returns to the caller a single value
"A,B,C" regardless if you add the quote to make it 'A', 'B', 'C'.
Even with that extra step, the single value is returned to the caller
as "'A', 'B', 'C'".

Has anybody managed to actually get this or something similar to work?

I am testing this with a fully patched access 2000 / Jet.

An explanation here would be helpful.
Chas

[CDMAPoster]

ChasW wrote:

It works great. You just don't have any OpID values that equal the
string "A, B, C" :-). It looks like you need strQ around each letter.
For example,

Case 1
strVal = strQ & "A" & strQ & ", " & strQ & "B" & strQ & ", " & strQ &
"C" & strQ

so that strVal = "A", "B", "C"

James A. Fortune
CD********@FortuneJames.com
I do not see how this can work "great" even with your correction.

I am trying to do the same as the original poster, but am finding that
a function with a type String returns to the caller a single value
"A,B,C" regardless if you add the quote to make it 'A', 'B', 'C'.
Even with that extra step, the single value is returned to the caller
as "'A', 'B', 'C'".

Has anybody managed to actually get this or something similar to work?

I am testing this with a fully patched access 2000 / Jet.

An explanation here would be helpful.
Chas

From the OP's example I would change

SELECT tblOp.OpID, tblOp.OpSkill
FROM tblOp
WHERE (((tblOp.OpID) In (finclause(1))));

to

"SELECT OpID, OpSkill FROM tblOp WHERE OpID IN (" & finclause(1) & ");"

Access SQL seems to get a little too literal with what's inside the
parentheses.

James A. Fortune
CD********@FortuneJames.com

[Phil Latio]

I'm still persuing this one, although somewhat rather slowly, and
grateful for the previous assistance provided so far.

I have my function in a Standard module.
The function is declared as Public.

When I call the function from a saved query it doesn't seem to run!
How do I know this?
When putting a breakpoint in the function's code, I expect (on running
the query) for it to halt execution at the breakpoint within the
function code!

I've checked and double checked the function name is correct in the
quey criteria, but still no joy!

Here's the function and SQL to the saved query;
'=========CODE BEGINS==================
Public Function fINClause(intVal As Integer) As String
Dim strVal As String
Dim strQ As String

strQ = Chr(34)

Select Case intVal

Case 1
strVal = strQ & "A" & strQ & "," & strQ & "B" & strQ
Case 2
strVal = strQ & "C" & strQ & "," _
& strQ & "C1" & strQ & "," _
& strQ & "C2" & strQ & "," _
& strQ & "C3" & strQ & "," _
& strQ & "D" & strQ & "," _
& strQ & "D1" & strQ
Case Else
strVal = strQ & "E" & strQ

End Select

fINClause = strVal

End Function
'=========CODE ENDS==================

SELECT tblCallData.CallCode, tblCallData.OpID
FROM tblCallData
WHERE (((tblCallData.OpID) In (" & fINClause(1) & ")));

Any further pointers as to where to look at making this work?

CD********@FortuneJames.com wrote:

ChasW wrote:

It works great. You just don't have any OpID values that equal the
string "A, B, C" :-). It looks like you need strQ around each letter.
For example,

Case 1
strVal = strQ & "A" & strQ & ", " & strQ & "B" & strQ & ", " & strQ &
"C" & strQ

so that strVal = "A", "B", "C"

James A. Fortune
CD********@FortuneJames.com

I do not see how this can work "great" even with your correction.

I am trying to do the same as the original poster, but am finding that
a function with a type String returns to the caller a single value
"A,B,C" regardless if you add the quote to make it 'A', 'B', 'C'.
Even with that extra step, the single value is returned to the caller
as "'A', 'B', 'C'".

Has anybody managed to actually get this or something similar to work?

I am testing this with a fully patched access 2000 / Jet.

An explanation here would be helpful.
Chas

From the OP's example I would change

SELECT tblOp.OpID, tblOp.OpSkill
FROM tblOp
WHERE (((tblOp.OpID) In (finclause(1))));

to

"SELECT OpID, OpSkill FROM tblOp WHERE OpID IN (" & finclause(1) & ");"

Access SQL seems to get a little too literal with what's inside the
parentheses.

James A. Fortune
CD********@FortuneJames.com

[CDMAPoster]

Phil Latio wrote:

I'm still persuing this one, although somewhat rather slowly, and
grateful for the previous assistance provided so far.

I have my function in a Standard module.
The function is declared as Public.

When I call the function from a saved query it doesn't seem to run!
How do I know this?
When putting a breakpoint in the function's code, I expect (on running
the query) for it to halt execution at the breakpoint within the
function code!

I've checked and double checked the function name is correct in the
quey criteria, but still no joy!

Here's the function and SQL to the saved query;
'=========CODE BEGINS==================
Public Function fINClause(intVal As Integer) As String
Dim strVal As String
Dim strQ As String

strQ = Chr(34)

Select Case intVal

Case 1
strVal = strQ & "A" & strQ & "," & strQ & "B" & strQ
Case 2
strVal = strQ & "C" & strQ & "," _
& strQ & "C1" & strQ & "," _
& strQ & "C2" & strQ & "," _
& strQ & "C3" & strQ & "," _
& strQ & "D" & strQ & "," _
& strQ & "D1" & strQ
Case Else
strVal = strQ & "E" & strQ

End Select

fINClause = strVal

End Function
'=========CODE ENDS==================

SELECT tblCallData.CallCode, tblCallData.OpID
FROM tblCallData
WHERE (((tblCallData.OpID) In (" & fINClause(1) & ")));

Any further pointers as to where to look at making this work?

If I run a query called qryCallData

SELECT CallCode, OpID FROM tblCallData WHERE OpID IN (fINClause(1));

and create a record in tblCallData with CallCode = "222"
and OpID = "A","B"

I get:

CallCOde OpID
222 "A","B"

Yet if I run qryCallData2

SELECT CallCode, OpID FROM tblCallData WHERE OpID IN ("A","B");

the records with OpID = A or OpID = B are returned.

So assuming you are not creating the SQL using a string, the question
becomes:

How do I convince Access SQL that fINClause(1) is a list rather than a
single string?

I've never tried to do this. Perhaps use something like:

WHERE OpID = Split(fINClause(1), ",")

The A97 help file for the In Operator says:

expr [Not] In(value1, value2, . . .)

value1, value2 Expression or list of expressions against which you want
to evaluate expr.

This suggests the use of IN (expr1, expr2, ...) rather than IN (expr3)
with expr3 being a list.

If for some reason you can't create the SQL by using a string, try
using the Split function first. If that doesn't work, there may still
be a way to coerce fINClause to return multiple values by placing it
within a carefully constructed query or by creating a new table.

James A. Fortune
CD********@FortuneJames.com

[Phil Latio]

Cheers James for this.

Ultimately, I think I've taken the path of least resistance here,
trying to make something work which sounds good in theory but probably
could be acheived more elegantly.

With some thought, I'm going to make a function (that takes an integer)
and open a recordset (filtered by said integer), loop through the
recordset values to construct a string (of concatenated values) for use
in an IN clause which will be tagged onto a SQL string (again in VBA).
Then, change a temporary query querydef to take the correctly formed
SQL string, it ought for it to be 'Bob's your Auntie, jobs a good 'un!'

I do appreciate your time, effort and explanations.

Thanks

Phil

CD********@FortuneJames.com wrote:

Phil Latio wrote:

I'm still persuing this one, although somewhat rather slowly, and
grateful for the previous assistance provided so far.

I have my function in a Standard module.
The function is declared as Public.

When I call the function from a saved query it doesn't seem to run!
How do I know this?
When putting a breakpoint in the function's code, I expect (on running
the query) for it to halt execution at the breakpoint within the
function code!

I've checked and double checked the function name is correct in the
quey criteria, but still no joy!

Here's the function and SQL to the saved query;
'=========CODE BEGINS==================
Public Function fINClause(intVal As Integer) As String
Dim strVal As String
Dim strQ As String

strQ = Chr(34)

Select Case intVal

Case 1
strVal = strQ & "A" & strQ & "," & strQ & "B" & strQ
Case 2
strVal = strQ & "C" & strQ & "," _
& strQ & "C1" & strQ & "," _
& strQ & "C2" & strQ & "," _
& strQ & "C3" & strQ & "," _
& strQ & "D" & strQ & "," _
& strQ & "D1" & strQ
Case Else
strVal = strQ & "E" & strQ

End Select

fINClause = strVal

End Function
'=========CODE ENDS==================

SELECT tblCallData.CallCode, tblCallData.OpID
FROM tblCallData
WHERE (((tblCallData.OpID) In (" & fINClause(1) & ")));

Any further pointers as to where to look at making this work?

If I run a query called qryCallData

SELECT CallCode, OpID FROM tblCallData WHERE OpID IN (fINClause(1));

and create a record in tblCallData with CallCode = "222"
and OpID = "A","B"

I get:

CallCOde OpID
222 "A","B"

Yet if I run qryCallData2

SELECT CallCode, OpID FROM tblCallData WHERE OpID IN ("A","B");

the records with OpID = A or OpID = B are returned.

So assuming you are not creating the SQL using a string, the question
becomes:

How do I convince Access SQL that fINClause(1) is a list rather than a
single string?

I've never tried to do this. Perhaps use something like:

WHERE OpID = Split(fINClause(1), ",")

The A97 help file for the In Operator says:

expr [Not] In(value1, value2, . . .)

value1, value2 Expression or list of expressions against which you want
to evaluate expr.

This suggests the use of IN (expr1, expr2, ...) rather than IN (expr3)
with expr3 being a list.

If for some reason you can't create the SQL by using a string, try
using the Split function first. If that doesn't work, there may still
be a way to coerce fINClause to return multiple values by placing it
within a carefully constructed query or by creating a new table.

James A. Fortune
CD********@FortuneJames.com

[David W. Fenton]

"Phil Latio" <ph********@lycos.co.uk> wrote in
news:11**********************@i39g2000cwa.googlegr oups.com:

With some thought, I'm going to make a function (that takes an
integer) and open a recordset (filtered by said integer), loop
through the recordset values to construct a string (of
concatenated values) for use in an IN clause which will be tagged
onto a SQL string (again in VBA). Then, change a temporary query
querydef to take the correctly formed SQL string, it ought for it
to be 'Bob's your Auntie, jobs a good 'un!'

Why edit a QueryDef? Why not just assign the dynamically created SQL
string to the recordsource of the relevant object?

I see people editing saved QueryDefs all the time in DAO, and I've
never comprehended why people bother with this for dynamic
recordsources. The only situation where this is the case is with
graph objects, which can't really have their data sources set on the
fly. In that case, rewriting the QueryDef to which the graphs are
bound is the best method I've found.

But other than that, I just don't see writing a QueryDef as
something that anyone should ever be doing for purposes of dynamic
filtering.

Oh, one other case: if you want to write a QueryDef to point to a
different MDB's tables when there are no table links in the MDB
storing the QueryDef (using the FROM ... IN '...' syntax), that's
something that I have done. But it's the kind of thing that's done
only each time the locations change, not every time a filtered
recordsource is needed. And I've only done that when I needed to
nest the QueryDef drawing from the other MDB inside another (where
the [...]. As Alias syntax can't be used because of too many nested
layers and or unremovable brackets).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Phil Latio]

Hi David,

You ask,

"Why edit a QueryDef? Why not just assign the dynamically created SQL
string to the recordsource of the relevant object?"

Because I hadn't even thought of it <g>, and now you've mentioned it,
that is a much better suggestion.

Thank you for the comments and taking the time to point out possible
pitfalls.

Cheers.

Phil

David W. Fenton wrote:

"Phil Latio" <ph********@lycos.co.uk> wrote in
news:11**********************@i39g2000cwa.googlegr oups.com:

With some thought, I'm going to make a function (that takes an
integer) and open a recordset (filtered by said integer), loop
through the recordset values to construct a string (of
concatenated values) for use in an IN clause which will be tagged
onto a SQL string (again in VBA). Then, change a temporary query
querydef to take the correctly formed SQL string, it ought for it
to be 'Bob's your Auntie, jobs a good 'un!'

Why edit a QueryDef? Why not just assign the dynamically created SQL
string to the recordsource of the relevant object?

I see people editing saved QueryDefs all the time in DAO, and I've
never comprehended why people bother with this for dynamic
recordsources. The only situation where this is the case is with
graph objects, which can't really have their data sources set on the
fly. In that case, rewriting the QueryDef to which the graphs are
bound is the best method I've found.

But other than that, I just don't see writing a QueryDef as
something that anyone should ever be doing for purposes of dynamic
filtering.

Oh, one other case: if you want to write a QueryDef to point to a
different MDB's tables when there are no table links in the MDB
storing the QueryDef (using the FROM ... IN '...' syntax), that's
something that I have done. But it's the kind of thing that's done
only each time the locations change, not every time a filtered
recordsource is needed. And I've only done that when I needed to
nest the QueryDef drawing from the other MDB inside another (where
the [...]. As Alias syntax can't be used because of too many nested
layers and or unremovable brackets).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[DFS]

Phil Latio wrote:

Hi David,

You ask,

"Why edit a QueryDef? Why not just assign the dynamically created SQL
string to the recordsource of the relevant object?"
There is a situation: you may need the flexibility of assigning a query
(rather than a form) as the SourceObject of a subform. If you use a form,
you can only show the fields on the form. With a query you can show
whatever fields it returns; it's handy for crosstabs and other dynamic
queries where you won't know in advance the fields that will be returned.


Because I hadn't even thought of it <g>, and now you've mentioned it,
that is a much better suggestion.

Thank you for the comments and taking the time to point out possible
pitfalls.

Cheers.

Phil

David W. Fenton wrote:

"Phil Latio" <ph********@lycos.co.uk> wrote in
news:11**********************@i39g2000cwa.googlegr oups.com:

With some thought, I'm going to make a function (that takes an
integer) and open a recordset (filtered by said integer), loop
through the recordset values to construct a string (of
concatenated values) for use in an IN clause which will be tagged
onto a SQL string (again in VBA). Then, change a temporary query
querydef to take the correctly formed SQL string, it ought for it
to be 'Bob's your Auntie, jobs a good 'un!'

Why edit a QueryDef? Why not just assign the dynamically created SQL
string to the recordsource of the relevant object?

I see people editing saved QueryDefs all the time in DAO, and I've
never comprehended why people bother with this for dynamic
recordsources. The only situation where this is the case is with
graph objects, which can't really have their data sources set on the
fly. In that case, rewriting the QueryDef to which the graphs are
bound is the best method I've found.

But other than that, I just don't see writing a QueryDef as
something that anyone should ever be doing for purposes of dynamic
filtering.

Oh, one other case: if you want to write a QueryDef to point to a
different MDB's tables when there are no table links in the MDB
storing the QueryDef (using the FROM ... IN '...' syntax), that's
something that I have done. But it's the kind of thing that's done
only each time the locations change, not every time a filtered
recordsource is needed. And I've only done that when I needed to
nest the QueryDef drawing from the other MDB inside another (where
the [...]. As Alias syntax can't be used because of too many nested
layers and or unremovable brackets).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"DFS" <nospam@dfs_.com> wrote in
news:Gt******************@bignews1.bellsouth.net:

Phil Latio wrote:

You ask,

"Why edit a QueryDef? Why not just assign the dynamically created
SQL string to the recordsource of the relevant object?"

There is a situation: you may need the flexibility of assigning a
query (rather than a form) as the SourceObject of a subform. If
you use a form, you can only show the fields on the form. With a
query you can show whatever fields it returns; it's handy for
crosstabs and other dynamic queries where you won't know in
advance the fields that will be returned.

Huh? Oh, I see that I'm not up to speed on something that was added
by Access 2000. That is, I had no idea you could assign a saved
querydef as sourceobject of a subform control.

That would be a relevant reason to save and edit a QueryDef, but
only just barely, in my opinion.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/