Results 1 to 6 of 6
  1. #1
    bliever is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2009
    Posts
    3

    Pass list as parameter to in operator

    Can't seem to pass a list of values to an in statement. Only get first value.

    parameter @List as text

    criteria in([@List])

    tried "val1" , "val2", "val3" and 'val1', 'val2', 'val3' and val1, val2, val3

    best result was returned val1 from query



    in design window I can use in("val1", "val2", "val3") but not in(
    [List])

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    To pass the contents of a lost box to a meaningful string that can be read using the In() command you need to do the following:

    Enumerate throught the contents of the listbox concatenating the column in question using a semi-colon sperator into a string variable. You can then wrap the in command around this string.

    Example
    Code:
     
    Dim sstr as String
    For x = 0 to list.Listcount -1
       sstr = sstr & ";" & me.list.column(1)
    Next
     
    sstr = mid(sstr,2)
    David

  3. #3
    bliever is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2009
    Posts
    3
    Thanks but Access 2007 does not allow ; as a delimiter.

  4. #4
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    So change it to a character that it does allow. The usage of the semi-colon was for brevity only.

    David

  5. #5
    bliever is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2009
    Posts
    3
    I did that of course. Perhaps I haven't made myself clear. I'm passing the parameter as a string from VB. The same string that works in the ide in("xxx", "XXX") as criteria does not work when passed as a parameter even within the ide in([@List]).

  6. #6
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Never seen Access programmed using

    parameter @List as text

    criteria in([@List])

    What back type are you using?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pass a Parameter From a form to a Query
    By DDillesha in forum Forms
    Replies: 1
    Last Post: 10-28-2009, 12:49 PM
  2. Replies: 1
    Last Post: 10-07-2009, 07:36 AM
  3. I want to automatically pass a value to a query
    By Slategrey252 in forum Queries
    Replies: 1
    Last Post: 10-01-2009, 05:38 AM
  4. Making the like operator work for fields
    By olidav911 in forum Queries
    Replies: 2
    Last Post: 06-06-2009, 01:57 AM
  5. Missing Operator error
    By data123 in forum Forms
    Replies: 1
    Last Post: 03-15-2009, 04:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums