Access Rnd Function generate random number and decimal

This Access tutorial explains how to use Access Rnd Function to generate random number and create custom Function for random decimal number.

Access Rnd Function to generate random number

Access Rnd Function is for use in Access, Access VBA, Excel VBA, it is used to generate random number larger than 0 and smaller than 1 (1 and 0 exclusive).

Syntax of Access Rnd Function

Rnd[(number)]

Number Rnd generates
Not supplied or any positive number Generate a new random number
0 Return the last generated number
Any negative number Generate a new random number and use it every time

Example of Access Rnd Function – generate random number

It is not useful to use Rnd Function alone, because we ultimately want to generate a random number between two specific numbers, but not a random number between 0 and 1.

Use the below formula to generate a number bounded by lowerbound and upperbound (both inclusive).

Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
Formula Explanation
Int((10-1+1)*Rnd()+1) Generate random number between 1 and 10
Int((200-100+1)*Rnd()+100) Generate random number between 100 and 200

Access VBA Function – generate random decimal number

If you think the formula is too complicated, you can use the below custom Function. The purpose of this custom Function is to simulate Excel worksheet Function RandBetween, which allows you to simply use upperbound and lowerbound as Function arguments.

This custom Function offers an optional parameter that allows you to generate random integer or decimal number.

VBA code of custom Function – generate random decimal number

Public Function wRandomNumber(lowerbound, upperbound, Optional rndType = 1) As Double
    Randomize
    rndVariable = Rnd
    If rndType = 1 Then
        wRandomNumber = Int((upperbound - lowerbound + 1) * rndVariable + lowerbound)
    ElseIf rndType = 2 Then
        If (upperbound - lowerbound + 1) * rndVariable + lowerbound <= upperbound Then
            wRandomNumber = (upperbound - lowerbound + 1) * rndVariable + lowerbound
        Else
            Do While (upperbound - lowerbound + 1) * rndVariable + lowerbound > upperbound
                rndVariable = Rnd
                If (upperbound - lowerbound + 1) * rndVariable + lowerbound <= upperbound Then
                    wRandomNumber = (upperbound - lowerbound + 1) * rndVariable + lowerbound
                End If
            Loop
        End If
    End If
End Function

Syntax of custom Function – generate random decimal number

wRandomNumber(lowerbound, upperbound, [rndType])
lowerbound The minimum random number
upperbound The maximum random number
rndType Optional, indicate whether the random number is decimal or integer

value description
1 (default) Return a random integer
2 Return a random decimal number

Example of custom Function – generate random decimal number

Formula Explanation
wRandomNumber(1,100) Return a random integer between 1 and 100 (both inclusive)
wRandomNumber(100,200,2) Return a random decimal number between 100 and 200 (both inclusive)

Outbound References

https://support.office.com/en-us/article/Rnd-Function-503CD2E4-3949-413F-980A-ED8FB35C1D80

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *