How to "fill" the in(value1, value2, etc)

[meyou]

Hi,

I would like to know if there's a way to "fill" the in() function with values with user's input?

Basically, the user needs to search for candidates based on experience(min=1, max=6). To achieve that, the user runs a query and he's been asked how many experiences he's looking for. Depending on the answer(1 to 6), the system goes in a switch case and then "fill" the in() with dynamic values.

Is this possible?

Sincerely,

 

[Simon_MT]

It's a little bit tricky but I found this ages ago and modified it:

Public Function GetSelectedRecords()
'       This function is designed to concatenate Records with a field to be used later for both IN Statements and QueryStrings
'       The forms contain the scope of the array Records2Select and SelectNum signifies to use String or Numeric values i.e. add quotes
'       In ALL cases the default value is zero and if the function finds a zero it knows when to stop!
Dim intI As Integer
Dim intN As String
Dim intV As String
Dim MyObject As Object
    Set MyObject = CodeContextObject
 
        For intI = 1 To MyObject![Records2Select]
            intV = MyObject(CStr(intI)).Value
            If MyObject![SelectNum] = "N" Then
                If intI = 1 And intV <> "0" Then
                    intN = "'" & intV & "'"
                ElseIf intI <> 1 And intV <> "0" Then
                    intN = intN & ",'" & intV & "'"
                End If
            Else
                If intI = 1 And intV <> 0 Then
                    intN = intV
                ElseIf intI <> 1 And intV <> 0 Then
                    intN = intN & "," & intV
        End If
            End If
            Next
        GetSelectedRecords = intN
 
End Function

With a number all you concatenate the number plus a comma
For strings you need the double quotes stringvariable single quote and comma

Have a fiddle with it!

Simon

 

[JANR]

Here is another way to do it with som help from a listbox to build a where-clausem to your SQL

http://www.fontstuff.com/access/acctut11.htm

JR

 

[meyou]

Ok,

I will have a look thanks.

Now, there's a thing I don't understand with functions and vba and design grid in Access.

How does it works! Let's say I have in the criteria line : IN (2, 3, 4). If I put In(functionToFillMyIN()) instead, can I expect functionToFillMyIn() to return values in the IN()?

Sincerely,