ms access - Create query in QueryEditor with VBA function call for specifying the WHERE IN clause -

i have written couple of vba functions in end return collection of integers:

public function validids() collection 

now want run create query in queryeditor following condition: where tableid in validids(). not work since access reason not find function long returns collection. therefore wrote wrapper around it, joins collection:

public function joincollectionforin(coll collection) string 

now third function calls validids(), passes result joincollectionforin , returns result. lets call getidcollectionasstring().

as result can change query where tableid in (getidcollectionasstring()). note added parenthesis since in needs them in case, can not @ end , beginning of string returned getid....

running query results in

data type mismatch in criteria expression.

i guess results fact return string, therefore access automatically wraps string in ' sql , in-clause no longer works because check if number in collection of 1 string.

therefore question is:

• is there way prevent access wrapping returned string sql

or (would whole lot better):

• is there existing way pass collection or array where in-clause?

p.s.: using workaround writing placeholder in parenthesis following in (e.g. in (1,2,3,4,5)) , replacing placeholder in form_load result of getidcollectionasstring() - works not pretty...

edit: final query should select * testtable t t.id in (1,2,3,4,5,6,7). works using above method, not in nice way.

well required more work seems.... couldn't find straight solution here workaround

public function listforin(inputstring string) string     dim qdf querydef     dim valcriteria string     dim strsql string     dim splitcounter byte     dim valcounter byte      set qdf = currentdb.querydefs(**thenameofqueryyouwanttomodify**)      strsql = qdf.sql     strsql = replace(strsql, ";", "") 'to rid of ";"     splitcounter = len(inputstring) - len(replace(inputstring, ",", ""))      valcounter = 0 splitcounter         valcriteria = valcriteria & valparsetext(inputstring, valcounter, ",")     next      strsql = strsql & " tableid in (" & left(valcriteria, len(valcriteria) - 1) & ")"     qdf.sql = strsql end function   public function valparsetext(textin string, x byte, optional mydelim string) variant     on error resume next     if len(mydelim) > 0         valparsetext = "val(" & (split(textin, mydelim)(x)) & "),"     else         valparsetext = split(textin, " ")(x)     end if end function