1

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

Carl
  • 5,569
  • 6
  • 39
  • 74
  • You mean `group in (3, 4)`? I don't know if Access supports multi-value parameters but that's probably what you should be looking at. You wouldn't need 4 trillion tests even if you ended up using VBA. – shawnt00 Apr 21 '15 at 17:53
  • https://accessexperts.com/blog/2013/09/23/are-you-in-or-are-you-out-not-in/ – Carl Apr 21 '15 at 18:03
  • The link says in(3, 4) is correct not in (3, 4) – Carl Apr 21 '15 at 18:03
  • 1
    The space is not significant and most SQL is written with it there. I was concerned about the comma though: you wrote `34` which might have changed this into an entirely different question. – shawnt00 Apr 21 '15 at 18:09
  • 2
    Here's is are some suggested approaches. You could also try another approach by populating another table with the values of your "groups" and using `IN (select group from )`. https://support.microsoft.com/en-us/kb/210530 – shawnt00 Apr 21 '15 at 18:12
  • The problem is when I set the parameter in the form equal to "3,4" it reads it as "34" and if I set it to "3, 4" with a space I get an error – Carl Apr 21 '15 at 18:12
  • It's probably doing some implicit casting from a string value to a numeric. The comma is probably being ignored as a thousands separator. The approach with a single parameter isn't going to word. Read the article if you want an approach based on substring matches. – shawnt00 Apr 21 '15 at 20:56

3 Answers3

3

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.

Community
  • 1
  • 1
ZX9
  • 898
  • 2
  • 16
  • 34
0

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
ZX9
  • 898
  • 2
  • 16
  • 34
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • How would I implement this? I entered this code into the module for my form, but how do I call it/use it? Thanks – Carl Apr 22 '15 at 18:15
  • Hi @Carl I've edited the question with some implementation details. You just need to run this sub to change the query (`Call MakeTheQuery`) and then [run the query](http://stackoverflow.com/questions/9929009/ms-access-execute-a-saved-query-by-name-in-vba). Make sure you replace all of the names of things, too. – ZX9 Apr 22 '15 at 18:42
  • @Carl I attempted to edit the above answer, seeing as it is more streamlined, but my edit was rejected. I've added implementation information on my answer. – ZX9 Apr 22 '15 at 20:20
  • @st78: Read the question and answer again, please. The value is pulled from the _Caption_ property which is beyond user control. – Gustav Jan 29 '19 at 08:46
0

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

greg
  • 11
  • 1