split field content in Query (1 Viewer)

[wv_guy_26301]

Using Acces 2007
Here is what I'm looking to do.

The database has a single field that has the following text

John Doe, jane doe, bob smith

when I run the query
I would like to get it to seperate each name into its own row in the same column

John Doe
Jane doe
bob smith

Thanks for any help you can give me

 

[G81]

using vba would be the way to go for this. You could use some trickery like instr to search for the commas and mid, left, and right to build up the variables which you could display.

Aren't you better off normalising though and creating a proper structure so fields like that aren't included???

 

[wv_guy_26301]

using vba would be the way to go for this. You could use some trickery like instr to search for the commas and mid, left, and right to build up the variables which you could display.

Aren't you better off normalising though and creating a proper structure so fields like that aren't included???

Thanks for your reply

under normal circumstance I would agree. but normalizing isn't an option I can do at this time.

do you have or know where I can find the vb script that would do what I'm looking to do?

 

[G81]

No, sorry I dont. I just found a great little function though, but I've never tried it before. I used to use a dynamic array and the instr function to do exactly what this does.

Dim sString As Variant
    
sString = Split(YourString, ",")

That'll basically build a dynamic array with all your names which are inbetween commas. So if:

YourString = "John Doe,jane doe,bob smith"

Then you'll get:

sString(0) = John Doe
sString(1) = jane doe
sString(2) = bob smith

 

[G81]

Oh I forgot, then you'll need to populate your listbox or however you want to display it. For a listbox it mwould be something like this:

dim i as integer
for i=0 to ubound(sString())
list1.additem sString(i)
next i

 

[wv_guy_26301]

Thanks I'll try it and see how it goes.