The returned average will now be very close to the desired value of 0.5.Īt this point, we should be able to apply some random order to a query. This will force a call of the expression for each record, and it will make the distribution even - which you easily can check out: Select *, Rnd(-Timer() * ) As RandomNumber From YourTable To solve both issues, include the primary key (typically ) in the expression: In fact, they can vary between 0.2 and 0.8! This will not result in average values close to 0.5. The uneven distribution can easily be demonstrated: First and most important, it will return the same number for all records, as the expression will be called once only second, it will not return values with an even distribution between 0 and 1. Select *, Rnd(-Timer()) As RandomNumber From YourTableīut this has two issues.
#Microsoft access vba code examples series
Again, this parameter value could be generated by Timer() to obtain different start points for the value series returned: Or, you can call Rnd() with a parameter having a negative value. To overcome this limitation, you can create a user-defined function that calls Randomize. And while you can use a function - user-defined or native of VBA - in an Access query, a statement, which is what Randomize is, can not. Until now, code has been VBA only, not SQL. To conclude, to obtain five seemingly random numbers, call the code like this: This still isn't random, though you would have to open the application and run the command at the exact same split second of the day, which is not likely to happen. Thus, a command, Randomize, exists that will set the start point to somewhere in the predefined sequence of pseudo-random numbers and that somewhere is controlled by the time of the day. Having the same values returned defeats any purpose of random. If you open the application and run this code, those five values will always be: The first five values can easily be listed: However, as the values are picked from a sequence, the first pick must start somewhere, and - if no precaution is taken - that will be from the start. S = 0 : For i = 1 to 1000000 : s = s + Rnd() :Next : ? s / iĪnd press Enter, and you will get results like these:Īll values are, as seen, very close to 0.5. Type into the Immediate Window this line: This means, that if you sum these and calculate the average, the result will be close to 0.5. They are picked from a long pre-generated list of numbers having an even distribution between 0 and 1. What this means is, that even if the generated numbers may appear random, they are not. This is what the native function of VBA, Rnd, offers. However, for our purpose, a less-than-ideal method can be used: pseudo-random numbers. If true randomness is mandatory for you, study my article Truly Random Numbers in VBA and, in the following, use the function RndQrn in place of Rnd.
It is important to understand, that generating truly random numbers is an art of its own, that still can keep mathematicians busy: Randomness. That is usually to select a small count of records from a large set of records where you don't want these to be sorted by something - it could be for statistics, sample quality control, or similar. Next question is why or for what purpose ? As the assigned numbers are random, when sorting on these, any other order will appear to be random.
Typically, the reason for assigning a random number to each record of a recordset is to be able to sort these in a sequence completely out of any normal order - like ID, customer number, purchase date, etc. The third is about Ranking rows in Microsoft Access. The second is about Sequential Rows in Microsoft Access. This is the first article on row numbers in Microsoft Access.