This Access tutorial explains how to generate random letter A to Z / a to z or a mix using Rnd Function.
You may also want to read:
Excel generate random letter A to Z using RANDBETWEEN
Access generate random letter A to Z using Rnd
In order to generate random letter, we have to make use of two Functions.
– Rnd
– Chr
In the below sections, I will briefly go going through these two Functions. For details, click on the link above.
Access generate random number using Rnd
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 |
Use the below formula to set lowerbound and upperbound for the random number.
Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
Access Chr Function to convert ASCII value to character
Access Chr Function converts ASCII value to a character. ASCII (American Standard Code for Information Interchange) uses 8-bit code units, an old encoding system which stores mainly numbers, lowercase letters a to z, uppercase letters A to Z, basic punctuation symbols, control codes. Many old systems still use this encoding system. 8 bit means the computer memory uses “8” digits with 1 and 0 combination (binary) to represent a character, 8 bits memory is equal to 1 byte.
Below is the ASCII value to character conversion for letter. For full conversion table, click here.
| ASCII | Char |
| 65 | A |
| 66 | B |
| 67 | C |
| 68 | D |
| 69 | E |
| 70 | F |
| 71 | G |
| 72 | H |
| 73 | I |
| 74 | J |
| 75 | K |
| 76 | L |
| 77 | M |
| 78 | N |
| 79 | O |
| 80 | P |
| 81 | Q |
| 82 | R |
| 83 | S |
| 84 | T |
| 85 | U |
| 86 | V |
| 87 | W |
| 88 | X |
| 89 | Y |
| 90 | Z |
| 97 | a |
| 98 | b |
| 99 | c |
| 100 | d |
| 101 | e |
| 102 | f |
| 103 | g |
| 104 | h |
| 105 | i |
| 106 | j |
| 107 | k |
| 108 | l |
| 109 | m |
| 110 | n |
| 111 | o |
| 112 | p |
| 113 | q |
| 114 | r |
| 115 | s |
| 116 | t |
| 117 | u |
| 118 | v |
| 119 | w |
| 120 | x |
| 121 | y |
| 122 | z |
Access generate random letter A to Z / a to Z
To generate random letter, first decide whether you need lower case only, upper case only, or a mix of both.
If you want to generate lower case only, use ASCII value 97 to 122.
CHR(Int ((122- 97+ 1) * Rnd + 97))
If you want to generate upper case only, use ASCII value 65 to 90.
CHR(Int ((90- 65+ 1) * Rnd + 65))
If you want to generate a mix of lower case and upper case, use the VBA custom Function in the following section.
Access VBA Code – generate random letter A to Z / a to z / a to Z
Public Function wRandomLetter(Optional rndType = 1) As String Randomize Select Case rndType Case 1 randVariable = Int((122 - 65 + 1) * Rnd + 65) Do While randVariable > 90 And randVariable < 97 randVariable = Int((122 - 65 + 1) * Rnd + 65) Loop wRandomLetter = Chr(randVariable) Case 2 wRandomLetter = Chr(Int((122 - 97 + 1) * Rnd + 97)) Case 3 wRandomLetter = Chr(Int((90 - 65 + 1) * Rnd + 65)) End Select End Function
Access VBA Syntax – generate random letter A to Z / a to z / a to Z
wRandomLetter(randType)
| randType | Optional. Choose whether you want to generate lower case, upper case or a mix of both. 1 or no input – a mix of both 2 – lower case 3 – upper case |
Access VBA Example – generate random letter A to Z / a to z / a to Z
| Formula | Explanation |
| wRandomLetter() | Generate a letter which can be lower case or upper case |
| wRandomLetter(1) | Generate a letter which can be lower case or upper case |
| wRandomLetter(2) | Generate a letter which is lower case |
| wRandomLetter(3) | Generate a letter which is upper case |
Outbound References
https://support.office.com/en-us/article/Rnd-Function-503CD2E4-3949-413F-980A-ED8FB35C1D80
