| 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 © 2017 Page updated Oct17