Random Picker required

Rachel Saunders
Rachel Saunders used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

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:
https://www.experts-exchange.com/articles/33030/Random-Rows-in-Microsoft-Access.html

Author

Commented:
Yes sorry, I tried to type it in as a topic but for some reason it wouldnt take
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
No problem.  I've changed them.

OH, and Welcome to the site!
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
You would use the RND() function in VBA to generate a number:

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/rnd-function

 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.

Jim.

Author

Commented:
Thanks, Ill give that a go and get back to you if I have any issues if thats ok?  R
John TsioumprisSoftware & Systems Engineer

Commented:
The Rnd() funtion will generate a random number between [0...1] e.g
0,301948
0,2895625
0,5795186
0,533424
0,7055475
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)

Author

Commented:
Thankyou, Ill give that a go :-)
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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?

Jim.

Author

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
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

Commented:
Thank you :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial