Home	Excel	Access
\| Feedback \| Using Access Queries \|

Random numbers in an Access query

Access contains a function which supplies random numbers. If this is used in a query it will generate a single value which is then displayed on all of the records. The second field in this example is define as: TestOne: Rnd()

My query		ox
	Expense	TestOne
	Dressings	0.13566
	Linen	0.13566
	Drugs	0.13566

This is efficient in terms of processing resources but may not be what is required. To put a different random value in each record a small user defined function is required.

My query		ox
	Expense	TestTwo
	Dressings	8908
	Linen	2456
	Drugs	1012

In this second query the user defined function replaces the simple function: TestTwo: RandomNo([Expense])

Function 
	RandomNo(AnyField As Variant) As Integer
 Randomize (Now())
 RandomNo 
	= Rnd() * 10000
End Function

Although AnyField / Expense doesn’t directly impact on the result, it does force the function to recalculate for each record – otherwise it would return a single value and post it in each of the records - as with the first example. This function also transforms the result from a decimal into an Integer between 0 and 9999.

file: Acc_Random.htm

Page updated Oct17