我有几个输入,这是再上一个查询通过的形式。这是pretty的直白,但我想我的参数之一,被用在声明中在我的SQL像这样的:
I have a form with several inputs, that are then passed on to a query. This is pretty straightforward, except I want one of my parameters to be used in an IN statement in my sql like such:
Select sum(id) as numobs from table where year=[form]![form1]![year] and (group in([form]![form1]![group]));
在[表]![Form1中]![组] =3,4这querys组(34),如果[表]![Form1中]![组] =3,4,那么我得到一个错误说:这当然pression输入错误,或者是过于复杂,无法评估。
When [form]![form1]![group]="3,4" it querys "group in(34)" and if [form]![form1]![group]="3, 4" then I get an error saying "This expression is typed incorrectly, or it is too complex to be evaluated..."
我希望能够进入一个字段的形式由逗号分隔的多个号码,然后有一个查询使用结果的陈述。这看起来是可行的?
I would like to be able to enter multiple numbers separated by a comma in a field in a form, and then have a query use the result in an IN statement. Does this seem doable?
我知道用VBA我能做的if-then语句来看看一组数字的每一个可能的组合(有超过40组,以combinatorically有超过4万亿的方式到40+基团结合,因为42的总和选择k从0到42的超过4万亿美元),以便使用IN语句似乎是一个更好的选择。
I know with VBA I could do if-then statements to look at every possible combination of group numbers (there are over 40 groups so combinatorically there are over 4 trillion ways to combine the 40+ groups since the sum of 42 choose k from 0 to 42 is over 4 trillion) so using the IN statement seems like a better option.
在如何让报表的任何想法,从一个形式参数的工作?
Any ideas on how to get the IN statement to work with a parameter from a form?
感谢
这可以非常简单地用一分的VBA模块完成的:
This can be very simply done with a sub in a VBA module:
Sub MakeTheQuery()
Dim db As DAO.Database
Dim strSQL As String
Dim strElements As String
Set db = CurrentDb()
strSQL = "SELECT Sum(id) AS numobs " & _
"FROM ErrorKey WHERE ErrorKey.ID In ({0});"
' Example: "3,5"
strElements = Forms!YourForm!YourControl.Caption
' Assign SQL to query
db.QueryDefs!YourQuery.SQL = Replace(strSQL, "{0}", strElements)
End Sub