How to generate random number each time?

Access Database Random Number generation

• I want to generate a randomly generated number field in Microsoft Access, which will change each time a report or query is used

• Answer:

  Baldrick, I have a cunning plan! (sorry couldn't resist). There are two approaches you can take to this problem : 1) Write a query (my method) 2) Write a macro (the method I found by doing a google search for ["randomly select records" access]) Here's the idea behind my method : a) Append to each record a random number b) sort by that random number c) take the first x records As an example my database has the following table : table name: "final sample" fields: divid, permno, idate divid is a unique identifier (like an autonumber field). This is important to have because we use it in a wierd manner to generate our random numbers. To do this I use the following query : SELECT TOP 100 [final sample].divID, [final sample].permno, [final sample].idate FROM [final sample] ORDER BY Rnd([DIVID]); Unfortunately, the 100 is hard coded into the query - ideally you'd like it to ask you before you run the query. You can't use a parameter in place of the "100" (I tried it, it didn't work). So your alternatives are : 1) Manually change the query code each time before you run it 2) Write a macro that uses the RunSQL command and a dialog box to get the input. Hopefully this helps - if you want me to go into more technical details feel free to request clarification. As I said, there are some sites on the web that give you alternative methods using VisualBasic code. They aren't as intuitive (in my humble opinion). But for completeness you might want to check this forum post : Randomly Selecting Records http://www.experts-exchange.com/Databases/MS_Access/Q_10253413.html Regards calebu2-ga Search terms (within Google) "randomly select records" access Search terms (within the Access help) : Rnd TOP