[AccessD] Using a Function in a WHERE IN clause

Joe,

    For a string composed of comma separated values based upon multi-select list box, straightforward application of IN operator in a saved query won't do, as the individual selected elements come through as submerged in a single string. For proper interpretation, the criteria string has to be processed via EVal() function. For smooth implementation of this approach, it is however necessary that each individual element in comma separated string is enclosed in outer single quotes (if text type) or hashes (if date type). No special treatment is needed for number type data.

    If text type data is likely to contain embedded quotes, it is necessary to replace each such occurrence of single quote by two single quotes before finally enclosing the value in outermost single quotes. For embedded double quotes if any, no special treatment is needed.
    
    Three alternative styles of applying the search criteria, using  IN  operator, could be adopted: 
    (a) SQL string built at run time. 
    (b) Form's filter string applied at run time. 
    (c) Permanent saved query with embedded criteria clause. 

    All the alternative methods outlined above are demonstrated in my sample db named  Form_SearchByMultipleListBoxes.  It is in access 2000 file format and is available at Rogers Access Library.  Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Rojas, Joe 
  To: accessd at databaseadvisors.com 
  Sent: Tuesday, January 05, 2010 23:22
  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>