Random Picker required

I need a function/macro that will randomly pick a video link from a database but have no clue how to get started, can anyone help please?
Rachel SaundersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You mention database but have Excel as a Topic Area.  Can I assume Access not Excel?

There is an article here on the site that talks about querying random rows from Access:
Rachel SaundersAuthor Commented:
Yes sorry, I tried to type it in as a topic but for some reason it wouldnt take
slightwv (䄆 Netminder) Commented:
No problem.  I've changed them.

OH, and Welcome to the site!
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Jim Dettman (EE MVE)President / OwnerCommented:
You would use the RND() function in VBA to generate a number:


 If the video's are named numerically, then you could just choose a video based on that.  If not, then you'd need to load them in some type of a list (i.e. an array or collection), then the nth video from that list.

Rachel SaundersAuthor Commented:
Thanks, Ill give that a go and get back to you if I have any issues if thats ok?  R
John TsioumprisSoftware & Systems EngineerCommented:
The Rnd() funtion will generate a random number between [0...1] e.g
Suppose that your video links are stored in a table you could multiply this random number and get an integer pointer
E.g. your table has 100 entries
multiply it it with the 1st result
will give you
100* 0,301948 = 30,1948...convert it to integer you get it the 30th record
So simply by multiplying the Recordcount and the rnd() will give you a random selection of the video link
NorieAnalyst Assistant Commented:
If you are using Access you might be able to do this with a query, something like this.

SELECT TOP 1 [VideoLinkField] FROM [MyTable] ORDER BY INT(Rnd(Now())*10000)
Rachel SaundersAuthor Commented:
Thankyou, Ill give that a go :-)
Jim Dettman (EE MVE)President / OwnerCommented:
I guess the question for you is how you have these videos are indexed/cataloged; are they in a table or are they just out on disk?

Rachel SaundersAuthor Commented:
On a disk at the moment but they will be in a table.  I have a few in a table to play with at the mo
Gustav BrockCIOCommented:
If you wish to pick one random ID from the table, you can use DLookup this way:

Criteria = "[ID] = (Select top 1 ID From [YourTable] WHERE (RandomRowNumber(CStr([ID])) <> RandomRowNumber('',True))  Order By RandomRowNumber(CStr([ID])))"
RandomID = DLookup("[ID]", "[YourTable]", Criteria)

Open in new window

The RandomRowNumber function is from my article and demo:

Random Rows in Microsoft Access
Rachel SaundersAuthor Commented:
Thank you :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.