Unanswered: extracting items from a comma-separated list
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?
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)
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)