How do I send multiple values to a query?

[ajolson1964]

I have a form that has a text box where I want the user to be able to put in multiple values and pass that to a query. The query parameter is set to [Forms]![Ad_Hoc_Report_frm].[TxtEnter_PID_Filter] When I put in more than one value (int values) I get an error.

Example

If I put in one value 1234 it returns record 1234 as expected.

If I put in 1234,2934, or any thing like that in (1234,2934) I get this error message: The expression is typed incorrectly, or is too complicated to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning part of the expressions to a variable.
I am new to access not sure how to assign to a variable.
Any help would be greatly appreciated.


Thanks

[ItsMe]

That is because you are including a comma within your expression. It seems you are dealing with number data types in your SQL and as soon as you type the comma, SQL interprets this differently.

What you are creating, when the user is typing multiple numbers separated by a comma, is a string array. I am not aware of an Array function in Access. I think there is one function that you can use to parse the elements. I have used it before but I would have to search for it.

[ranman256]

The BEST way is to have a list. The user picks items, or enters them in a table. This table is joined to your query table.
It pulls the only those in the list.
just like saying where [code] = 65,or 76 or 32 or 124

[ajolson1964]

Thanks what is the setting for them to select more than one item on the list?

[orange]

If you insert into a textbox tbxExample 81,32, 45,67
You can use a Split function to separate the values, then you can use those individual values
as you wish.

Here's a small example that may help

'---------------------------------------------------------------------------------------
' Procedure : tbxExample_AfterUpdate
' Author    : mellon
' Date      : 18/05/2015
' Purpose   : Sample form with textbox to show use of split() to
'             get individual values from a string of "csv" values.
'             Once you have individual values you can use them/manipulate them as required.
'
'Revised: To show use of specific value in the textbox and also
'         how to get individual values between the commas.
'---------------------------------------------------------------------------------------
'
Private Sub tbxExample_AfterUpdate()
          Dim x As Variant
          Dim i As Integer
          Dim t1 As String
          Dim t2 As String
10        t1 = "This is a mockedup SQL -"
20        t2 = "SELECT * FROM MyTable WHERE someIntfield In("
30        On Error GoTo tbxExample_AfterUpdate_Error
          
      'use the string value in the textbox
40        Debug.Print "Sample using the text box string " & vbCrLf & vbCrLf & t1 & t2 & Me.tbxExample & ")" & vbCrLf & vbCrLf _
          & "OR  individual values beteeen commas " & vbCrLf
          
                   'OR
      'separate the individual integers for individual processing/manpulation

50        If InStr(Me.tbxExample, ",") > 0 Then    'there is at least a comma in the txtbox
60            x = Split(Me.tbxExample, ",")
70            For i = LBound(x) To UBound(x)
80                Debug.Print i; x(i) & "  " & x(i) ^ 3 & " is " & x(i) & " cubed"
90            Next i
100       Else
110           MsgBox "no values in textbox", vbOKOnly
120       End If
130      On Error GoTo 0
140      Exit Sub

tbxExample_AfterUpdate_Error:

150       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure tbxExample_AfterUpdate of VBA Document Form_FormSampleTextBoxWithCSV"

End Sub

Output:
Sample using the text box string

This is a mockedup SQL -SELECT * FROM MyTable WHERE someIntfield In(81,32,45,67)

OR individual values between commas

0 81 531441 is 81 cubed
1 32 32768 is 32 cubed
2 45 91125 is 45 cubed
3 67 300763 is 67 cubed

NOTE: This was revised based on June7's comment and to show processing the individual values between the commas.

[June7]

AFAIK, parsing the CSV string to an array won't help with a dynamic parameterized query. The query filter criteria needs the IN operator in conjunction with CSVs and this won't work with a dynamic parameter. However, can use the CSV string to programmatically set the filter property of form or report.

[orange]

I was just showing an example of how to enter comma separated values into a textbox.
Process them, and adjust some recordsource sql with an IN clause, and separating out each integer value.

Select fields from MyTable where YourIntField In ( a,b,c..)

If the Op has users entering 2,4,5,6,7 in a textbox and wants to select certain records, I think this example can do it.

Note: The code and post #5 have been revised to clarify
a) using the entire string of comma separated numbers en bloc to create an IN(...) clause, and
b) separating and using individual values (integers) in subsequent process(es).

Thanks June for the comment.

Note: If these were text values between commas, you would have to enter "value1","value2"....
or programmatically process the textbox string to make individual strings for an IN clause.

The essence of my responses was to show how you could have a comma separated list in the text box and process it. Some programming behind the scenes is required.

[June7]

I just tested code to set form Filter property without first parsing string through an array. It works.

I NEVER use dynamic parameterized queries.

[ajolson1964]

Thanks all. I took a simpler approach. Built a table where the user can paste their values in then runs a inner join query. Works like a charm, And part of the requirement was to allow the user to enter up to 5000 records at a time. So this solved all problems and potential problems I might have had. Thanks for the help.