[AccessD] Using a Function in a WHERE IN clause

Is PersonID a numeric value? If so then your Where Clause should be of this form

WHERE localPartPlant.PersonID In (1,2,5,77,88,3,1023) etc. etc and GetPlannerCodes() needs to return a string of coma separated digits, with no quotes of any kind.

To achieve this you need to use something like this as the where clause...

WHERE localPartPlant.PersonID In ( & GetPlannerCodes() & )

But you cannot use syntax like that directly in a query, because what you are reall need to do is modify the SQL string of the query. So you need to do that in code. I would do so by building the query with a dummy criterion like this....

IN(IDTOKEN)

Access will change that to read In ("IDTOKEN") and the full where clause will be WHERE (((Policy_Pipeline_tbl.nBuyStatus) In ("IDTOKEN")));

Save that query as a template. Now in code you can do this

1/ Copy the template query
	docmd.CopyObject ,"FinalOutputQuery",acQuery ,"TemplateQuery"

2/ Open the copy of the query and change the SQL to what you need...

Dim qd as QueryDef
Dim sSQL as String 
	Set Qd = CurrentDb.QueryDefs("FinalOutputQuery")
	sSQL = Qd.SQL
	sSQL= Replace(sSQL,""IDTOKEN"",GetPlannerCodes())
	Qd.SQL = sSQL
	Qd.Close
	
3/ And then you can run the modified query.

Lambert




-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rojas, Joe
Sent: Tuesday, January 05, 2010 12:52 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Using a Function in a WHERE IN clause

Hello,

I am trying to use a custom function in a IN clause of a query.
The function cycles through a multi-select list box and builds a string based on what the user picked.
The WHERE clause looks like this: WHERE localPartPlant.PersonID In (GetPlannerCodes())

The problem I'm having is figuring out how the string should be formatted when more than one item is selected.
When one item is select, the string has to have no quotes, no double quotes, and no commas. For example, CX If I do this with one item, the query works.

When multiple items are selected, I have tried the following string formats:
"CX", "CX1"
'CX', 'CX1'
CX, CX1
CX', 'CX1
CX", "CX1

All to no avail.

Anyone know how to achieve this?

Joe Rojas
Information Technology Manager
Symmetry Medical New Bedford
P: 508.998.4575
M:508.838.4717
F:508.995.9597
joe.rojas at symmetrymedical.com<mailto:joe.rojas at symmetrymedical.com>

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com