Carl Carl - 4 months ago 6
SQL Question

Using parameter from form in IN statement

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]));


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?

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?

Thanks

Answer

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