Query using one text box with multiple entries

[archer1900]

Hey everyone,

I was looking to run a query using one text box with multiple entries. For example : EP342, EP423, EP234. Is there a way to do this in VB?

 

[Miranda1]

Can you be a bit more specific about what you need your query to do?

Do you use the text box to provide parameters for the query? If that is the case, you're better off providing a text box for each parameter.

If you are trying to query a text field that has multiple values in it, you really should do something about the underlying table design so that each field in a record has only one value in it. It'll make it much easier to work with the data.

 

[archer1900]

Can you be a bit more specific about what you need your query to do?

Do you use the text box to provide parameters for the query? If that is the case, you're better off providing a text box for each parameter.

If you are trying to query a text field that has multiple values in it, you really should do something about the underlying table design so that each field in a record has only one value in it. It'll make it much easier to work with the data.

You are correct I use the text box to provide parameters for the query. I thought I could just enter in the text box TEST, TEST2, TEST2 and quickly realized that could not work.

Thank you for the reply!

 

[archer1900]

I will try this and get back to you with the results, thank you!!

 

[Miranda1]

Ok, think I understand what you're trying to do. Maybe you can try something along the following lines: assign the input from your text box to a string in VBA. Use the split function to split the string on the commas and assign the values to an array. Loop through the array and for each value in the array, assign the value to the WHERE clause of your query (if that is where it needs to go). Execute the query and do something useful with the result.

Dim strInput As String
Dim arrParam As Variant
Dim strSQL As String
Dim i As Integer

'assign the text in your text box to this variable
strInput = "whatever your string of input values is"

'splits the string when it encounters a comma, assigns to array
'this only works if the parameters are always separated by commas!
arrParam = Split(strInput, Chr(44))


'loops through the array and appends each value in the array to the where clause of the query SQL
'and runs the query.
For i = 0 To UBound(arrParam)
    
    'Build the query you need. 
   'The trim is in there to get rid of leading and   trailing spaces
    strSQL = "SELECT * FROM query WHERE ID=" & Trim(arrParam(i))
    'execute the query
    DoCmd.RunSQL strSQL

Next