Randomly Select records

[Stephen Simons]

Thanks to John Vinson I have got the following module doing exactly what I want. Many thansk, John!

Would it be possible to refine it by having the query ask, via a dialog box, the number of records I want to randomly
select?

I've had a look at some of the samples, but cannot quite get there. A pointer in the right direction would be
appreciated.

TIA

Steve

From a dbs containing several thousand records, I would like to be able to choose all that meet a certain criteria (say
one field is equal to, or greater than, zero); and then from the records that meet that criteria, randomly select a
sample of say 20.

I can achieve this relatively easily in XL, using formulae and some VBA code.

Can anyone point me towards the right approach for Access?

You can use the Top Values property of a query, with help
from a little VBA. Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see.


remove "_ILY_" to email me

email to (e-mail address removed)

 

[Van T. Dinh]

You cannot use a Parameter for the TOP Clause of the Query.

However, you can construct / modify the SQL String of the Query. I think
the following should work:

1. Create a Form "frmParameter" with TextBoxes so that the user can enter
the TOP value as well as values for other Parameter. Also have a
CommandButton to execute the Query.

2. In the CommandButton_Click Even, construct / modify the SQL String of
the Query using the QueryDef object to access the Query. Thus after you
construct the SQL String by code, the Query will have the TOP value and
non-parametrised as you use the values in the Textboxes to replace the
Parameters. You can then simply run the Query by code using DoCmd.OpenQuery
Method, assuming that you only want to open the DatasheetView of the Query.

3. When the user wants to run the Query, open the Form "frmParameter" first
so that he/she can enter the TOP value as well as Parameter values. Once
finished, he/she can simply click the CommandButton to open the Query.

Check Access VB(A) Help on the QueryDef and OpenQuery Method. Note: you
need to add the DAO Object Library into the References Collection of your
database.

--
HTH
Van T. Dinh
MVP (Access)

Thanks to John Vinson I have got the following module doing exactly what I want. Many thansk, John!

Would it be possible to refine it by having the query ask, via a dialog

box, the number of records I want to randomly

select?

I've had a look at some of the samples, but cannot quite get there. A

pointer in the right direction would be