Access generate random letter A to Z using Rnd

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)

Access VBA Example – generate random letter A to Z / a to z / a to Z

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

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

Access generate random letter A to Z using Rnd