bobpeers -> blog

Sometimes for testing it’s useful to be able to insert random numbers into a table field. This is an easy way to do this in MS Access. First write a custom function that looks like this.

Function random(ID as Integer,min as Integer,max as Integer) As Integer
random = Int((max - min + 1) * Rnd + min)
End Function

The max and min are the lowest and highest random numbers you wish to generate, ID is a field from the table (it can be any field) you wish to insert the random number into.

Of course if your field is not an integer then you will have to change the ID as Integer part to ID as String for example.

The field is required so the function is called on every row, otherwise Access will just run the function once and then update every row with the same random number.
To call the function from SQL use this.

Update my_table set random_field=random(ID, 1, 10)

In this case I’ve used an Update statement to insert a number between 1 and 10 into the field called random_field but it works equally for for Insert statements.