函数为我的IN子句提供标准 [英] Function to supply the criteria for my IN Clause
问题描述
可能不是最好的方法，但是如果有人
可以告诉我为什么以下DOESN''T工作我会很感激;
我有一个函数（下面的代码），它返回一个基于
提供的整数值的字符串。
我有一个SELECT查询（下面的SQL）我想要的在IN子句中使用函数
。
奇怪的是，我不想要一个解决方案，为什么它不起作用
我没问题！
非常感谢
Phil
公共函数fInClause（intVal As Integer）As String
Dim strVal As String
Dim strQ As String
strQ = Chr （34）
选择案例intVal
案例1
strVal = strQ& A，B，C &安培; strQ
案例2
strVal = strQ& Z，F &安培; strQ
案例3
strVal = strQ& Q，T &安培; strQ
案例4
strVal = strQ& D，R，V &安培; strQ
结束选择
fInClause = strVal
结束功能
SELECT tblOp.OpID，tblOp.OpSkill
FROM tblOp
WHERE（（（tblOp.OpID）In（finclause（1））））;
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))));
推荐答案
Phil Latio写道：
Phil Latio wrote:
可能不是最好的方法，但是如果有人能够告诉我为什么会这样做，我会很感激的''工作;
我有一个函数（下面的代码），它返回一个基于
提供的整数值的字符串。
我有一个SELECT查询（下面的SQL）我想在IN子句中使用
函数。
奇怪的是，我不想要一个解决方案，为什么它不起作用呢
做我很好！
非常感谢
Phil
公共函数fInClause（intVal As Integer）As String
Dim strVal As String
Dim strQ As String
strQ = Chr（34）
选择案例intVal
案例1
strVal = strQ& A，B，C &安培; strQ
案例2
strVal = strQ& Z，F &安培; strQ
案例3
strVal = strQ& Q，T &安培; strQ
案例4
strVal = strQ& D，R，V &安培; strQ
结束选择
fInClause = strVal
结束功能
SELECT tblOp.OpID，tblOp.OpSkill
FROM tblOp
WHERE（（（ tblOp.OpID）In（finclause（1））））;
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))));
效果很好。你只是没有任何等于
字符串A，B，C的OpID值。 :-)。看起来每个字母都需要strQ。
例如，
案例1
strVal = strQ& " A" &安培; strQ& ， &安培; strQ& " B" &安培; strQ& ， &安培; strQ&
" C" &安培; strQ
所以strVal =" A"，" B"，" C"
James A. Fortune CD********@FortuneJames.com
在90年代初期，我一直在寻找是否有任何汽车，摩托车或
重型机械公司对我所申请的专利感兴趣
co-发明者。我尝试了一个名为
#Nippon的组的IRC Relay Chat。大约凌晨4点。日本产业高管们正在聊天频道使用
举行商务会议。一上台登录，所有
登录名（约12个）就像草原狗消失一样注销了。
我虽然这很有趣所以我一直登录并等待一个
几分钟。一位来自多伦多的男子登录并开始和我聊天
英文。他突然意识到我可能不是日本人，所以他告诉我，你认为日本应该成为这个星球上唯一的经济实力吗？我解释说我不是日本人而且我只是在那里告诉他们我正在为一种新的能量回收传输申请专利。
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.
效果很好。你只是没有任何等于
字符串A，B，C的OpID值。 :-)。看起来每个字母都需要strQ。
例如，
案例1
strVal = strQ& " A" &安培; strQ& ， &安培; strQ& " B" &安培; strQ& ， &安培; strQ&
C &安培; strQ
以便strVal =A，B，C
James A. Fortune
CD ****** ** @ FortuneJames.com
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
我看不出这是如何工作的伟大的即使你的更正。
我试图做与原始海报相同的，但我发现
a函数与类型字符串返回给调用者单个值
A，B，C无论你是否添加引号使它成为'A''，''B''，''C''。
即使有了额外的步骤，单个值也会返回给调用者
为''''，''B''，''C''"。
有没有人设法实际得到这个或者类似于工作的东西？
我正在使用完全修补的2000 / Jet接入测试。
这里的解释会有所帮助。
Chas
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
ChasW写道：
ChasW wrote:
它很棒。你只是没有任何等于
字符串A，B，C的OpID值。 :-)。看起来每个字母都需要strQ。
例如，
案例1
strVal = strQ& " A" &安培; strQ& ， &安培; strQ& " B" &安培; strQ& ， &安培; strQ&
C &安培; strQ
以便strVal =A，B，C
James A. Fortune
CD ****** **@FortuneJames.com
我看不出这是如何工作的伟大的即使你的更正。
我试图做与原始海报相同，但我发现
一个类型为String的函数返回给调用者一个值
" A，B，C"无论你是否添加引号使它成为'A''，''B''，''C''。
即使有了额外的步骤，单个值也会返回给调用者
"'''''，''B''，''C''"
有没有人设法真正得到这个或类似的工作？
我正在使用完全修补的2000 / Jet进行测试。
这里的解释会有所帮助。
Chas
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
来自OP'我要改变的例子
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）&"）;"
访问SQL似乎与
括号。
James A. Fortune
CD ******** @ FortuneJames.com
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
这篇关于函数为我的IN子句提供标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
