Generate random numbers in Access

Random numbers are useful for more than just games programming. You often need to generate random numbers in Access in order to create some test figures or to select a typical sample of data. The VBA Rnd() function and the Randomize() statement allow you to generate random numbers and give you control over the sequence that is generated.

These functions give you random numeric values between zero and one but you can adapt them to generate a random number within any numeric range, to generate a random date, or to generate a random logical value of True or False.

Warning

Note that Access does not really generate any random numbers. The numbers appear to be random but they are being generated by a mathematical algorithm and the fact that we can regenerate exactly the same sequence of 'random' numbers as many times as necessary proves that they cannot be truly random. Anyone who knew the details of the algorithm and knew the starting point of the calculation would be able to predict the next number that will be produced by a call to the Rnd() function.

What this means is that you would not want to use Rnd() for something like a commercial lottery where large sums of money depend on the numbers being unpredictable and truly random. For such work you need a more sophisticated tool and the services of a skilled statistician. But, having said that, the random number features of Access are very useful for the sort of tasks described here.

Using Rnd()

The Rnd() function takes a single numeric parameter and returns a number between zero and one. The number generated is of type Single and the range includes zero but not one. The behaviour of the function depends on whether the parameter provided is negative, zero or positive:

Negative parameter

Access will use a negative parameter as the seed for its random number generator. If you call Rnd() several times with the same negative parameter then it will return the same random number value each time. For example:

will always return 0.224007. Use a negative seed number like this when you want to generate some random test data but need to be able to repeat these tests exactly by generating the same series of random numbers again.

Zero parameter

A parameter of zero will make Rnd() return the same random number as was generated on its previous call.

Positive parameter

A postive parameter, or no parameter at all, will make Rnd() return the next random number in the sequence generated by the random number generator built into Access. If you issue this sequence of commands:

then you will always generate the following sequence of random numbers:

Using Randomize

Randomize is a statement which initialises the random number generator with a seed value based on the system clock. If you need a different series of random numbers each time you run a test then issue a Randomize command once before the first call to Rnd(). There is no need to issue the command before each random number is generated. A single call at the start of the sequence ensures that a new set of random numbers will be created.

Getting an integer in a particular range

The Rnd() function gives you a random number between 0 and 1 but most of the time you are going to want a number within a wider range of values. For example, if you want an integer between 1 and 100 then just multiply Rnd() by 100 and use Int() to convert each result to an integer:

This of course is not quite right. The range of numbers generated by Rnd() includes zero but does not include 1. Multiplying the result by 100 will bring us very close to 100 but Int() will always pull 99.9999... back to 99. We need to add 1 to the expression to make the range 1..100:

This will generate random numbers in the range 1 .. 100.

The general expression to generate a random integer in a particular range is:

where intLower and intUpper are the lower and upper limits of the range.

Random dates

The Rnd() function will only generate a numeric value but if you use the technique above to get an integer in a particualr range then it is easy to use the DateAdd() function to get a date which is a random number of days away from a given date:

Note that both these examples include the possibility of zero being generated and might produce today's date. You might need to include an offset of 1 to prevent this.

True or False

Generating a random value of True or False is also easy:

This will give an equal balance between True and False but you can bias the distribution by using a different number in place of 0.5. The higher the number, the less likely you are to get a larger number out of Rnd() and the fewer True values will be generated.