Using an array in where clause

[Chaga]

Hello everyone, I need to run a query that is filtered by an array, but is not returning the desired result.
I have a textbox "txtValues" on a form, that has the array values let's say 1,4,5,6
I need to fire up the query where the ID is within those value, so ran the SQl statement adding "where ID In ([Forms]![Contacts]![txtValues])"
It's currently not returning anything. Am I missing something out?
If I replace [Forms]![Contacts]![txtValues] with 1,4,5,6 it works just fine, so it seems like am missing some kind of syntax.
Thanks

 

[The_Doc_Man]

SQL does not inherently support arrays. The USUAL way to deal with this is to create a table with the values in question.

Let's say you have a table called tblArray and it contains your values 1, 4, 5, and 6 in separate records in field SelID. Let's say that the main table has a field called MainID and you want to select the records with ID values as enumerated in tblArray. That would look like this:

SELECT MainID, Mainfield1, Mainfield2, etc. FROM tblMAIN WHERE MainID IN ( SELECT SelID FROM tblArray )

Of course, select the actual field names, because I just generated "air code" as a demo only.

There IS such a thing as creating an ADO memory-based table, but for DAO (the default recordset style for Access databases) I would recommend the IN clause method.

 

[Minty]

Is this a saved query or one you are constucting in VBA ?

 

[Chaga]

Is this a saved query or one you are constucting in VBA ?

saved

 

[Chaga]

SQL does not inherently support arrays. The USUAL way to deal with this is to create a table with the values in question.

Let's say you have a table called tblArray and it contains your values 1, 4, 5, and 6 in separate records in field SelID. Let's say that the main table has a field called MainID and you want to select the records with ID values as enumerated in tblArray. That would look like this:

SELECT MainID, Mainfield1, Mainfield2, etc. FROM tblMAIN WHERE MainID IN ( SELECT SelID FROM tblArray )

Of course, select the actual field names, because I just generated "air code" as a demo only.

There IS such a thing as creating an ADO memory-based table, but for DAO (the default recordset style for Access databases) I would recommend the IN clause method.

I don't wanna go into that much honestly.
I am using a function that will export a query to excel, and I just need to filter the result as per the active form. No further use for it.
The query record source is different from the form's, that's why I don't want to straight export the form's result.

 

[theDBguy]

Hi. Not sure if this will help but you can take a look.

Create a Parameter Query to accept multiple values

Parameter queries are a quick way to create dynamic queries where the result changes depending on user input. If you are not familiar with p...

thedbguy.blogspot.com

 

[Chaga]

Hi. Not sure if this will help but you can take a look.

Create a Parameter Query to accept multiple values

Parameter queries are a quick way to create dynamic queries where the result changes depending on user input. If you are not familiar with p...

thedbguy.blogspot.com

From the article I could deduct the following being my issue

and expecting the user to simply enter 1,3,5 when prompted. Unfortunately, this seemingly straightforward approach will not work because Access will interpret the entire input as a String or Text. So, entering 1,3,5 does not mean 1 or 3 or 5; but rather, a string value of "1,3,5" - and we know there won't be any DeptID of "1,3,5" because DeptID is a numeric field. So, the query will result in an empty set because the intended criteria fails.

But sadly no solution to my problem.

 

[theDBguy]

But sadly no solution to my problem.

If the article applies to your situation, then I don't know what you mean. The point of the article was to provide a solution. Are you saying you tried it, and it didn't work for you?

 

[Chaga]

If the article applies to your situation, then I don't know what you mean. The point of the article was to provide a solution. Are you saying you tried it, and it didn't work for you?

No, the article mentions how the textbox values will be interpreted as a string and not as numbers (because as I said, when I manually enter the numbers instead of referencing the textbox, it works fine), so I need to figure out how to have the query read the array values as numbers and not as a string.
All the examples on the page, are based on manually inputting the array, and none on a predefined one.
@The_Doc_Man mentioned that SQL does not inherently support arrays, and I just need to know if there is some easier workaround to the solution he proposed.

 

[cheekybuddha]

so I need to figure out how to have the query read the array values as numbers and not as a string.

Try the 'Solution' section in DBG's linked post.

Just substitute your textbox reference for the [Enter ...] parameter.

 

[theDBguy]

No, the article mentions how the textbox values will be interpreted as a string and not as numbers (because as I said, when I manually enter the numbers instead of referencing the textbox, it works fine), so I need to figure out how to have the query read the array values as numbers and not as a string.
All the examples on the page, are based on manually inputting the array, and none on a predefined one.
@The_Doc_Man mentioned that SQL does not inherently support arrays, and I just need to know if there is some easier workaround to the solution he proposed.

Hi. What @cheekybuddha said. If you get stuck, let us know. You can post a screenshot to show us what you did. Cheers!

 

[Chaga]

Try the 'Solution' section in DBG's linked post.

Just substitute your textbox reference for the [Enter ...] parameter.

I've tried the following since my array is comma separated, and it didn't work.

Where InStr("," & [Forms]![Contacts]![txtValues] & ",","," & "ID" & ",")>0

 

[The_Doc_Man]

There IS the syntax like this, using IN (list) as a WHERE criterion:

SELECT MainID, MainField1, MainField2, ..., FROM tblMain WHERE MainID IN (n1, n2, n3, n4..., nx) AND ... (other WHERE criteria)

In operator (Microsoft Access SQL)

Office VBA reference topic

learn.microsoft.com

So if you got a list like "1,3,5" you could build the SQL dynamically OR you could make the In-list a query parameter, then assign the parameter value to the query before executing it. This is the simplest it gets. If this is even too much then I've got nothing else I can offer.

More precisely, put a parameter inside the parentheses containing n1, n2, etc. and then assign the query parameter value to contain the string you needed, with commas. You STILL cannot get it to look at an array, but you CAN give it list if you get the format correctly. Here is a link on how to define and programmatically fill in a parameter based query.

Is it possible to pass parameters programmatically in a Microsoft Access update query?

I have a query that's rather large, joining over a dozen tables, and I want to pull back records based on an id field (e.g.: between nStartID and nEndID). I created two parameters and tested them as

stackoverflow.com

 

[MajP]

I love when people come for help, do not try the suggestion, then criticize it
Works completely for me.

SELECT tblcategories.catid,
       tblcategories.red,
       tblcategories.green,
       tblcategories.id
FROM tblcategories
WHERE (( "," & [forms] ! [form2] ! [txtfilter] & "," LIKE "*," & [id] &
",*" ))
[code]

 

[cheekybuddha]

Easier to see using code tags:
Where InStr("," & [Forms]![Contacts]![txtValues] & ",","," & "ID" & ",")>0
Try:
Where InStr("," & [Forms]![Contacts]![txtValues] & ",","," & ID & ",")>0

 

[cheekybuddha]

OR you could make the In-list a query parameter, then assign the parameter value to the query before executing it. This is the simplest it gets

If I understand what you are suggesting correctly, I don't think this will work (most annoying that it doesn't!), which is where DBG's solution comes in to play.

But perhaps, I have misunderstood ...?

 

[The_Doc_Man]

CB, I'm taking a shot at using the parameter to drop a string into the parentheses following an IN operator. If the number list is dropped in but VBA follows its normal rules, the string "1,3,5" will be dropped in as a string 1,3,5 because the outer quotes get stripped across that equals-sign that would assign the parameter value. That means that the string becomes ...IN ( 1,3,5 ) ... - and because literal numbers are treated ambiguously in SQL, they might be interpreted as a list of numbers in that context. That is the closest I can come to what is requested. And if that doesn't work then I've got nothing else to offer because the OP is reluctant to take the "external table" route to dynamically build a list.

 

[cheekybuddha]

That means that the string becomes ...IN ( 1,3,5 ) ...

You can only do this if you construct or modify the SQL in VBA.

A saved parameter query with SQL:
SELECT * FROM YourTable WHERE ID IN ( & [Forms]![Contacts]![txtValues] & );
won't work as expected/desired.

 

[CJ_London]

You can use the eval function

WHERE eval( id & “ in (“ & forms!myform!choices & “)”) = true

 

[cheekybuddha]

Really, I'm with Doc - better to use a little VBA to construct a proper IN clause which will have the benefit of utilising any indexes present on the searched field.