Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Posts
    4

    Unanswered: extracting items from a comma-separated list

    Hello,

    Is there a function, or combination of functions, I can use in an Access query to extract an item from a comma-separated list that is stored in another field? Essentially, I want something like the Choose() function, except that Choose expects all the things it's choosing from to be presented as arguments. If I do Choose(1,[myfield]), it returns the entire contents of myfield. I want something that will realize myfield contains a comma-separated list, and choose from among the items in that list. (In ColdFusion, it would be the ListGetAt() function).

    Does anybody know of such a beast or a way to do it otherwise?

    thanks!
    Monika

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Take a look at the Split() function in the help files
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2007
    Posts
    4

    can't find it

    well, based on its name, it's exactly what I want....but I don't appear to have it. Is it in some special Add-on that you have to install?

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by mols
    well, based on its name, it's exactly what I want....but I don't appear to have it. Is it in some special Add-on that you have to install?
    No, it's a built in function. CF's ListGetAt function returns the item (from memory, I haven't used CF in years), whereas split returns an array variant.

    VB won't let you use that array immediately; you have to assign it to a value. That means that you need something like this: (I'm not in front of a 'puter with Access at the moment, so I might have the order of parameters wrong)

    Code:
    function ListGetAt(CommaSepList as String, Item as Integer) as String
      dim SplitList as Variant ' you could drop the As Variant, but this makes it clear that you need a variant
      SplitList = Split(",", CommaSepList)
      ListGetAt = SplitList(Item)
    end function

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mols
    I want something that will realize myfield contains a comma-separated list, and choose from among the items in that list.
    so how do you know which one you want?

    and even more importantly, why is it stored that way?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2007
    Posts
    4

    thanks!!

    sco08y you are awesome!

    The parameters for split were reversed, but I got that going and made it so I could give it the delimiter as well. Thank you!!

    r937 - it is what it is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •