Specialists in Microsoft Access and Visual Foxpro database training and development

I am happy to hear from existing clients but I am taking no new calls.

March 2015

I have now retired and am taking on no new work. I'd like to thank all our customers over the past 20 years. It really has been a most enjoyable time.

Generate random numbers in Access VBA

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:

Rnd(
-1
)

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:

?Rnd(
-1
)
?Rnd()
?Rnd()

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

0.224007
3.584582E-02
8.635235E-02

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:

Int(
100 *
Rnd())

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:

1 +
Int(
100 *
Rnd())

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

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

intLower +
Int(
(intUpper - intLower + 1) *
Rnd())

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:

Rnd()
> 0.5

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.