[AccessD] Using a Function in a WHERE IN clause

Rojas, Joe Joe.Rojas at symmetrymedical.com
Thu Jan 7 10:38:48 CST 2010


Thanks for all the suggestions.

I ended up updating the queryDef.SQL to reflect the planner codes I need.

Joe

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Tuesday, January 05, 2010 5:59 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Using a Function in a WHERE IN clause

I think the better way would be to just skip the IN part:

WHERE SelectedPlannerCodes(localPartPlant.PersonID)=True

And then have your function determine if that ID is selected or not.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rojas, Joe
Sent: Tuesday, January 05, 2010 11:52 AM
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
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.


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





More information about the AccessD mailing list