Dynamically populate an "In( )"

[tbellomo]

OK...
I'm trying to builder a querier for someone who doesn't know Access. They select states from a list box, which populated the states "code" into a text box (like this: 1,2,3,6,30) That writes to a table. Now, those numbers need to serve as the criteria for my query. I've tried some ways of doing it so far:

In([StateCode]) -- but that produced a data type mismatch The criteria is querying a number field, and the aforementioned code produced In("1,2,3,6,30&quot

"In(" & [StateCode] & &quot" -- but that too produced a datatype mismatch.

I'm not sure what to try next. The number of values has to be dynamic, so I can think of a way to do it other thatn that. Any ideas?

--Timo

 

[gol4]

first you need to get the data from the list box

dim stListstr as srting
dim varitm as variant
dim strsql as string
for each varitm in me!list1.itemsselected
stliststr= stliststr me!list1.itemdata(varitm)& ","
next varitm
stliststr = left(stliststr,len(stliststr)-1) 'drop last comma
'now build your select

strsql = select * from table where statecode in (stliststr)

if you field type is test you will need to edit it so each one is surrounded by '
stliststr= stliststr me!list1.itemdata(varitm)& "','"
stliststr = &'& & left(stliststr,len(stliststr)-2)

good luck

 

[gol4]

Sorry on a closer read of your post it appears you already are building the string into a textbox

have you tried the instr function

your query criteria would be something like

instr(1,[statecode],[statefield]) >1

if numeric may need to couple with cstr()

 

[tbellomo]

i already have the values from the listbox stored into a state code table. I'm trying to query a numeric field in another table using the state code table record as my criteria ( "1,3,5,30" ). The quotes mess everything up. Note: it doesn't store with quotes, but when it's read in, the quotes throw it off.