MS Access database support - Function to supply the criteria for my IN Clause

Author

Function to supply the criteria for my IN Clause

Phil Latio

2006-03-09, 8:25 pm

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@FortuneJames.com

2006-03-09, 8:25 pm

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
CDMAPoster@FortuneJa
mes.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

2006-04-08, 7:27 am

>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
> CDMAPoster@FortuneJa
mes.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

Sponsored Links