| Feedback | Using Access Queries | |
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 | © meadinkent.co.uk 2017 | Page updated Oct17 |