Query parameter with comma separated numeric values

Question

Hello!

Could somebody give me your opinion on how to solve this?

I'm creating a Query. One of the parameters should be several comma separated integers (e.g. 1,5,88,437,777,986) for use in a "in" clause (e.g. "and CardID in ([MyParam]"). There's no way to declare an array of integer parameter. Tried TEXT, but it doesn't work since it won't match the field type in the table.

So far, I'm thinking about creating a VBA function to return a in-memory table containing the values (one in each row) and use in the query.

Is there any better solution?

Answer 1

you could make a function and pass cardID and MyParam to that function.

Inside the function you can convert the string list into valid numeric values to make your comparison then return True or False back to the query

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 2

I don't think this approach would work... I'm using [MyParam] to filter the results, not to test if it's true or false.

[MyParam] values (e.g. 1,54,55,774) will be used to filter rows using CardID field.

Answer 3

It's always a test of true or false. (IT either meets the criteria - or it doesn't - even using the "IN" clause)  If it is true, then you want that record.  So for your criterion you call the function in the field column and in the criterion you put true.  If the function returns true, it will match the criterion and the record will be included in the query.

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 4

Here's the function

Function myInList(ValueToCheck As Integer, ListToCheckAgainst As String) As Boolean

    Dim myListArray() As String
    myListArray = Split(ListToCheckAgainst, ",")
    For Each Item In myListArray
        If ValueToCheck = Val(Item) Then
            myInList = True
            Exit For
        End If
    Next Item
End Function

This is how you use it

SELECT tblTable.CardID, tblTable.Item
FROM tblTable
WHERE (((myinlist([cardID],[MyParams]))=True));

In a table that looks like this

<tfoot></tfoot>

tblTable

CardID	Item	address
1,	Test 1,	This is the address for test 1
2,	Test 2,	This is the address for test 2
3,	Test 3,	This is the address for test 3
4,	Test 4,	This is the address for test 4
5,	add1,	This is the address for test 5
6,	add2,	This is the address for test 6
7,	add3,	This is the address for test 7
8	add4	This is the address for test 8

With parameters passed like this 1, 5, 7, 9

Your return will be this

1, Test 1

5, add1

7, add3

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 5

I love it when I see a post and I have a dusty old query sample saved for exactly the right answer!

/***Remove comment block before running***
InStr tries to match OrderID to the string containing a list of OrderID's.
(InStr(",10251,10253,10254,", ",10255,"))>0
The >0 represents True; Use =0 or Not (((Instr...); to exclude the numbers passed
*/
SELECT Orders.*
FROM Orders
WHERE (((InStr("," & [Enter OrderID's (Comma Separated)] & ",","," & [OrderID] & ","))>0));

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 6

The list used with the In operator is a list of Values, not a string that looks like a list.  In general, remember that a parameter must be a Value, it can not any expression operators or other syntax.

You can get the desired effect by using either of the where conditions:

   WHERE ("," & [MyParam] & ",") Like "*," & CardID & ",*"
or
   WHERE InStr(("," & [MyParam] & ","), ("," & CardID & ",") > 0

Or, you could use IN this way:

   WHERE Eval(CardID & " IN(" & [MyParam] & ")")

Answer 7

Here's the function

Function myInList(ValueToCheck As Integer, ListToCheckAgainst As String) As Boolean

    Dim myListArray() As String
    myListArray = Split(ListToCheckAgainst, ",")
    For Each Item In myListArray
        If ValueToCheck = Val(Item) Then
            myInList = True
            Exit For
        End If
    Next Item
End Function

This is how you use it

SELECT tblTable.CardID, tblTable.Item
FROM tblTable
WHERE (((myinlist([cardID],[MyParams]))=True));

In a table that looks like this

<tfoot></tfoot>

tblTable

CardID	Item	address
1,	Test 1,	This is the address for test 1
2,	Test 2,	This is the address for test 2
3,	Test 3,	This is the address for test 3
4,	Test 4,	This is the address for test 4
5,	add1,	This is the address for test 5
6,	add2,	This is the address for test 6
7,	add3,	This is the address for test 7
8	add4	This is the address for test 8

With parameters passed like this 1, 5, 7, 9

Your return will be this

1, Test 1

5, add1

7, add3

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

For some reason the For Each is not working... I had to replace it for:

Dim i as Integer

For i = 0 To UBound(myListArray)

...

Next i

I even tried to declare Item, but didn't work too.

Answer 8

was there an error message?  if so, what was it.  What version of Access are you using?  I'm not sure if split was available in older versions.

to use for i = ...

you will need to use the myListArray[i] to check the value, but unless I know how it's not working I can't help.  I did test the code in Access 2010 and it works fine for me.

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 9

was there an error message?  if so, what was it.  What version of Access are you using?  I'm not sure if split was available in older versions.

to use for i = ...

you will need to use the myListArray[i] to check the value, but unless I know how it's not working I can't help.  I did test the code in Access 2010 and it works fine for me.

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Using Access 2003 here. It's an awkward message error since I'm calling from a Query (shows a generic message, not what happened inside the function). Using For i=o to... works. Thank you very much!

Answer 10

It's an awkward message error since I'm calling from a Query (shows a generic message, not what happened inside the function).

Using For i=o to... works. Thank you very much!

So? is it working now or are you still getting an error?

If you're getting an error, put a break point inside the function and then step through as look at the values of each variable to consider what might be wrong.

---

For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

Answer 11

Microsoft provides a couple of solutions at:

http://support.microsoft.com/kb/100131/en-us

The second method, involving the InParam and GetToken functions, is more reliable as it avoid any possible mismatches due to a value being a substring of another value.

---

Ken Sheridan, Stafford, England