How to select a song randomly?

[Speed Pete]

Again I need some help for my first scripting attempts...

I want to select randomly a song with a specific Genre. I know I can get the number of apropriate songs like this:

Code: Select all

  SQL = "SELECT Count(*) As Nombre FROM Songs WHERE Genre=100004"
  Set Iter = SDB.Database.OpenSQL(SQL)
  max = CLng(iter.StringByName("Nombre"))

And I know, I can "filter" for the apropriate songs like this:

Code: Select all

  SQL = "SELECT ID FROM Songs WHERE Genre=100004"
  Set Iter = SDB.Database.OpenSQL(SQL)

But how can I now select one of these songs randomly? I do not know a function that gives me the song in row number x
- this would help.

[trixmoto]

Having got your Iter you could call Iter.Next a random number of times to get a random record. I can't really think of a better way of doing it than that.

Code: Select all

Randomize
i = Int(max*Rnd)
For j = 0 To i
  Iter.Next
  If Iter.EOF Then Exit For
Next
Set itm = Iter.Item

[psyXonova]

Trix, you should have learned by now that anything that has to do with a record set can be done with SQL...
In this spesific case...

EDIT (the bellow code has been edited to filter the results based on genre name)

Code: Select all

Function GetRandomGenreSong(strGenreName)
Dim RandomNumber, SQL, iter
Randomize
RandomNumber = Int (1000*Rnd)+1
'we have to create a random feed for the Rnd function in Access since the seed is cached per session, thus giving as the same result every time
'but if we supply the Rnd with a negative seed it will force it to give different results everytime

SQL = "SELECT TOP 1 Songs.ID, Rnd(" & -1 * RandomNumber & "*Songs.ID) FROM Songs INNER JOIN Genres ON Songs.Genre = Genres.IDGenre WHERE Genres.GenreName = '" & strGenreName & "' ORDER BY 2"

Set iter = SDB.Database.OpenSQL(SQL)
' you can now iterate the recordset which only contains one record and get the id of your random song

End Function

[trixmoto]

I knew someone would come up with some clever solution and show up my lack of SQL knowledge again!

[Speed Pete]

Lol, then why did you splitted your question in 2 different posts....

Sorry - One step at a time. So I encountered first the problem to get Genre Number to the Name, and later I tried to select a song randomly. Just didn't espect that it could be done in ONE.

I thank you both very much for the clear and easy version and for for the clever one...

Before I open another thread: can you just tell me how (whether) I can make an array of strings?

[trixmoto]

You certainly can make an array. Here's what DevGuru have to say on the matter.

A simple example would be...

Code: Select all

Dim myarray : myarray = array("A", "B", "C", "D")

... but it depends how you want to build the array.

[Speed Pete]

THANKS! Now I have anything I need.

As far as I understood, it would be fine to make a script here available for the public. Now I have used some code from your hints here, and - in first - my script is based on Trixmoto's AutoAlbumDJ.
Is it ok, to publish my script here?

[psyXonova]

Its better to start a new thread with a proper description of your script, full source code and offcourse credits to Trix for letting you use his script as a start...

[trixmoto]

Yes, starting a new thread for a new script is certainly a good idea.

Thanks for asking, but as long as there is a small acknowledgement in your source code, you are free to use any of my code.

[Speed Pete]

Wow - once again for your help. I used this script for 7 years now!!! But: now we git a new PC and I installed MM 4. And now I ran into problems with the old script .

When I start the script I got this error message:

Error executing SQL "SELECT TOP 1 Songs.ID, Rnd(-462*Songs.ID) FROM Songs INNER JOIN Genres ON Songs.Genre = Genres.IDGenre WHERE Genres.GenreName = '5-Samba' AND Rating>=-20 ORDER BY 2" : near "1": syntax error (1, 1)

And this is, what the script says in this line:

Code: Select all

SQL = "SELECT TOP 1 Songs.ID, Rnd(" & -1 * RandomNumber & "*Songs.ID) FROM Songs INNER JOIN Genres ON Songs.Genre = Genres.IDGenre WHERE Genres.GenreName = '" & strGenreName & "' AND Rating>="&MinRating*20&" ORDER BY 2" 

On the old PC with MM 2, the script worked fine. Can you give me a hint, why it does not on MM 4?

Thank you,

Pete