在声明中使用的形式参数 [英] Using parameter from form in IN statement

问题描述

我有几个输入，这是再上一个查询通过的形式。这是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

这篇关于在声明中使用的形式参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！