Fred Hooper
fahooper at gmail.com
Tue Jan 5 12:06:04 CST 2010
Joe, there may be a more elegant solution but I'd construct the whole SQL statement and use it rather than using a function embedded in the statement. Alternatively, for greater speed I'd populate a table with the selected values and join to it (if you have multiple users then place the table you're editing in the front end so each user has his/her own). It's faster because "IN" statements have to check each element while joins are better optimized. Fred On Tue, Jan 5, 2010 at 12:52 PM, Rojas, Joe <Joe.Rojas at symmetrymedical.com>wrote: > 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 >