Is there a way to split a field and query the split results?

[James D]

I have created a Internet Music Station using the Winamp device in HB with over 6000 stations. It uses an exterior access database for it to work. I have only one problem (on the exterior database; name radiostations) I have a field named Genre for multiple different category of the radio stations. The field has each Genre(category) separated by a comma (ex.DANCE,TRANCE,RNB) In the database I have a few queries built in. The field name is "Genre" on the main table of the database and displays (example "DANCE,TRANCE,RNB") for a selected station.

Is there a way to make a query of the Genre field on the Main Table but split them so it ....BLAH BLAH BLAH

I want:

DANCE,TRANCE,RNB

to go to

DANCE
TRANCE
RNB


Attached is a screen shot of what my database is like. I hope I am making sense.

[wetwired]

Assuming you´re using vbscript? Make the query, put the result in a variable and split it into an array.

Code: Select all

' global variable to store string value
Dim myString

' comma separated string value assigned to the variable
myString = "str1,str2,str3,str4,str5"

' variable to store the result of Split function
Dim myArray

' split function with delimiter parameter as comma ","
myArray = Split(myString,",")
 
Dim counter
 
for counter = 0 to UBound(myArray)
    Response.Write(myArray(counter) & "<br />")
next 

Take a look att this page:
http://programming.top54u.com/post/ASP- ... mples.aspx

[James D]

I will look into this today. Thanks for the help. Is there a way to query a query in access? Still kind of new to Access Databases. I am trying to have the external database (RadioStations.mdb) do all the robust work (like queries and sorts), and then have HB pull the information I need. Is that the best way?

[wetwired]

Well, if you really want to have simple queries to get the data out of the database you shouldn´t have fields that contain different values. Design the db to be simple to query.

A quick and dirty way in your case would be to have more than on field containing the genre values, "genre1", "genre2","genre3" etc... In each of these fields you put the "DANCE","TRANCE","RNB". Then the queryresult would contain these values already separated.

The best way and correct way would be to have a different table containing genres and stationid fields and then use the stationid to query all the correct genres depending on station selected. This is a bit more complicated though and I have only done this in mysql and sql databases.