Using parameter from form in IN statement

Question

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 1

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

Answer 2

I can't figure out a way to do it with IN.

My solution: in a VBA module in your database, write a sub to build a query based on the values in the form control. Let's use the Split method to make an array, which we can iterate through to build a query.

Sub MakeTheQuery()
Dim strSQL As String, db As DAO.Database
Set db = CurrentDb()

strSQL = "SELECT sum(id) AS numobs _
FROM table WHERE ("

Value = Forms!YourForm!YourControl.Caption
'The property may vary with controls ^

'Create an array from the control values
anArray = Split(Value, ",")

'Add to the query for each value
For i = LBound(anArray) To (UBound(anArray) - 1)
strSQL = strSQL & " ErrorKey.ID = " & anArray(i) & " OR"
Next i

'Wrap it all up
strSQL = strSQL & " ErrorKey.ID = " & anArray(UBound(anArray)) & ")"

'Assign SQL to query
db.QueryDefs!YourQuery.SQL = strSQL

End Sub

Please keep in mind this assumes that the string from the form control will be formatted with no spaces, but you can easily change that by using anArray = Split(Value, ", ") instead (note the space add after the comma).

To assign the VBA sub to a button or other control on your form by going to Layout View, clicking on a control, going to the Event tab on the Property Sheet, hitting the ... button beside the On Click. Hit Code Builder. Put Call MakeTheQuery in the sub that it builds for you. If you want to run the query, too, put DoCmd.OpenQuery "YourQuery".

You can also use this method to build a more complex, dynamic parameter query.

Answer 3

You may consider Instr() function instead of In. Assuming you are generating the numbered list procedurally rather than forcing the end user to type a comma-separated list, you can change the format. We typically use pipe separators, so for example, the input may look like this: [form]![form1]![group]="|3|4|" This is passed to the query as a single string parameter and tested in the Instr() function as follows: Instr([form]![form1]![group],"|" & group & "|")>0 ' Instr() returns a numeric value of the position in the first argument where the second argument is found. The pipes ensure we don't get false positives (for example, finding 3 when we pass 34). We use this with multi-select listboxes and a custom VBA function, but it's perfectly viable any time you have control over the format. Instr Function - support.office.com