Randomly select records

[GussyBoy]

I have a table of about 20000 records. I want to randomly select 100 records from the 20000 and echo the 100 records to a report. I have also included a Yes/No field type. This will be used to identify the 100 selected records. I have created an ID (Autonumber) field marking the number of records (1 thru 20000). This is wat I have so far....

Dim MyValue
Dim ctr As Integer

ctr = 1

Do While ctr <> 100

MyValue = Int((20000 * Rnd) + 1)

ctr = ctr + 1

Loop


My dilemna is how do I associate the content of MyValue to the ID field so that I can run a query to update the Yes/No field?

Please help and advise.
Thanks.

 

[lewds]

Hi..

A possible solution...

1. create a standard module
2. declare MyValue as a global variable
3. write a function for the variable

Public Function FnMyValue()
FnMyValue = MyValue
End Function

4. create a updatequery for your yes/no field

Include in your query the autonumber field and the Yes/No
field. Use FnMyValue() as criteria for the autonumber and
update the yes/no as you desire

Insert into your loop the Docmd.OpenQuery "YourQueryName" to
run after MyValue gets a value

 

[GussyBoy]

Lewds....
Thanks. I'll try it out. But I sont understand the need for the public function. Can you explain why I need it please?

Thanks again for your help.

 

[Creepers]

One thing to keep in mind. Use the word Randomize before you use Rnd.

 

[GussyBoy]

I'm uncertain of teh syntax of the criteria. Should it be =Myvalue()?

 

[GussyBoy]

This is what I have thus far.....

Option Compare Database
Dim myvalue As Integer


Private Sub Report_Open(Cancel As Integer)

Dim ctr As Integer
ctr = 1


Do While ctr <> 101
Randomize
myvalue = Int((18987 * Rnd) + 1)
DoCmd.OpenQuery "updateselect"
ctr = ctr + 1
Loop

End Sub

Public Function FnMyValue()

FnMyValue = myvalue
End Function

 

[lewds]

Hi..

The function allows for the variable to be used as the criteria of a query.

Unless you have a textbox on your form that you can reference or use direct SQL, this is a way to use a variable for what you want.

 

[lewds]

Hi...

Sorry, I did not read the rest of the post, just up to the point where you were unsure about the function.

The Public Function must be in a standard module

I see from what you replied that the function,although correct, appears to be in the form module with you other code

Global myvalue as integer... should also be in the standard module

The usage for the query is FnMyValue()

 

[GussyBoy]

Lewds,

I got it to work. I was scratching my head all this time wondering why I wasn't getting any data in my report. I was a simple mistake I was making.

I now have a better appreciation and understanding of passing parameters/values and the usage of global variables.

Thanks for all your help.