VBA - Random sample size based on user form input (1 Viewer)

[kwlmpa]

Good evening,

I am creating a form with the ability to display a random sample sized based on the user input in a textfield. Eg. User specifies they want 3 samples from a customers table; a random sample of 3 are shown as a query result.

I am having difficulty determining exactly why my code is failing and fails to produce any results:

Private Sub Command0_Click()
    Dim strSQL As String
     
     strSQL = "SELECT TOP " & Me.RandomNumberInput & " *" _
        & "FROM (SELECT Rnd(CustomerID) " _
        & "AS RandomValue, * " _
        & "FROM tCustomers) " _
        & "AS [%$##@_Alias] " _
        & "ORDER BY [%$##@_Alias].RandomValue;"
     Me.RecordSource = strSQL
End Sub

Thanks so much!
-k

 

[The_Doc_Man]

I'm not a moderator so cannot move this to a proper forum where it belongs, so I'll answer at least in part here. For future reference, the "Intro" forum is where you say Hello! The Access forums are where you ask technical questions. But you are new here and we don't hold that against you.

You probably are having trouble because Access really, REALLY, REALLY doesn't like special characters in names. Upper case alphabetic, lower case alphabetic, digits, and then underscore character should be your limit. Some of those other characters will confuse Access because % and #, for example, are special syntax "flags" - and if I recall correctly, $ also has special meaning in some contexts involving numeric contexts.

You also have an issue in that RND wants a SINGLE for its argument, and VBA will SILENTLY convert your customer ID (probably a LONG?) to a SINGLE number. It is entirely possible that you have no answers because RND is giving you fractions that don't match up to anything.

There is also the issue of placing CustomerID in the argument of RND(), but if you got customer ID 1, the RND value will be between 0 and 1 which is VERY LIKELY to be a fraction that won't work. Your scheme ALSO appears to require (by implication) that you have NO GAPS in your list of customer IDs. Because if you ever "retire" a customer, that would be a customer with an ID that you don't want to choose even though it is less than the highest ID ever assigned.

The last time I did something like this, I added a field to the table and wrote the random number to that field.

UPDATE table SET RandNumb = RND(10000) ;
SELECT TOP n ... FROM table ... WHERE ...  ORDER BY RandNumb;

Note, however, that you need to call the RANDOMIZE function and I don't know what/if it returns, so I don't know how to do that in a pure SQL environment. Since you are running your code via VBA, though, you can call RANDOMIZE before you run your SQL that involves the random numbers.

 

[pbaldy]

Moved to a more appropriate forum.

 

[MajP]

Sorry, there is some misinformation here.
The argument is a seed and has absolutely nothing to do with the range of values returned. You can pass in any numeric. Now() is often a good choice. The range is always a value between 0 and 1 regardless of the seed value. Yes you most certainly can use a customer ID or something that changes per record. Although the number is random the sequence based on a specific seed is not. Doing what Doc_Man shows and putting in a constant,will not work. You will get the same value for each record. Try it. You need a different seed per record. However, the remaining is correct. You should sort on the random field and take top x.

 

[CJ_London]

crossposted here

http://www.accessforums.net/showthread.php?t=73303

@kwlmpa - please read this link about the etiquette of cross posting https://www.excelguru.ca/content.php?184

 

[The_Doc_Man]

Sorry, MajP - different language variants. In some BASIC variants, that is a RANGE but in others it is (as you say) a seed. I answered that one last night because I was being my typical insomniac and was getting a bit bleary-eyed.

So instead of RND(10000), use INT(RND()*10000). Then my method would work.

 

[arnelgp]

strSQL = "SELECT TOP " & Me.RandomNumberInput & " *" _
& "FROM tCustomers " _
& "ORDER BY Rand(CustomerId);"
Me.RecordSource = strSQL

 

[MajP]

Depending how used this most likely will also not work

strSQL = "SELECT TOP " & Me.RandomNumberInput & " *" _
& "FROM tCustomers " _
& "ORDER BY Rand(CustomerId);"
Me.RecordSource = strSQL

As I previously stated if you seed the rnd with a constant, the function is run once and each record gets the same value. If you do above each record will get a different value but the series remains the same every time you open the db.

You need to roll your own rnd function

Public Function MyRnd(Optional Seed As Variant = 1, Optional NewSeries As Boolean = True) As Double
  'You have to seed it with a unique value per record if used in a query
  If NewSeries Then Randomize
  MyRnd = Rnd(Seed)
End Function

To prove this look at the demo one has a query with

SELECT TOP 10 Products.ID, Products.[Product Name]
FROM Products
ORDER BY [COLOR="red"]Rnd([id])[/COLOR];

The second

SELECT TOP 10 Products.ID, Products.[Product Name]
FROM Products
ORDER BY [COLOR="Red"]MyRnd([id])[/COLOR];

If I open the data base I can predict the first ten records for the case using rnd
94, 6, 72, 89, 1, 3, 34, 40, 65, 57
and if I requery you get
87, 80, 5, 8, 82, 92, 83, 57, 86, 97
and so on
So you will get 10 random records but the sequence will always be the same.
You may wonder why. For a lot of math and science work you want random variables, but need to be able to repeat the pattern to prove you results.

 

[arnelgp]

I think customerid is not Constant. And where are the same values returning from your result.

 

[MajP]

I think customerid is not Constant. And where are the same values returning from your result.

You need to read carefully and look at my demo. If you seed with with the customerID (as you should, or something that changed per record) you will get a unique value per record, but not a unique sequence. Close the db and reopen it. You will get the very same random sequence again. If you requery you well get the same next set of random numbers.

 

[MajP]

If you are not convinced open the database. And requery the first subform which passes in an ID but does not use my function

SELECT TOP 10 Products.ID, Products.[Product Name]
FROM Products
ORDER BY Rnd([id]);

When you open the database you will get the following 20 sequences of records for each requery

Val1	Val2	Val3	val4	Val5	val6	val7	val8	val9	val10
94	6	72	89	1	3	34	40	65	57
87	80	5	8	82	92	83	57	86	97
56	81	91	52	8	20	95	99	17	83
4	3	87	99	51	94	89	74	86	85
72	19	4	3	96	57	94	14	8	91
92	65	40	97	87	7	51	94	93	1
66	86	97	92	57	1	7	14	95	52
93	5	40	86	43	83	99	90	80	87
94	19	80	98	97	14	1	92	4	52
74	92	89	41	85	66	56	77	3	5
96	93	5	85	19	21	72	98	1	6
1	3	72	19	82	65	81	96	83	74
99	43	4	95	85	56	3	82	93	14
81	99	97	89	83	86	14	87	96	48
99	89	51	88	41	90	4	80	77	83
97	90	92	21	7	85	72	57	5	41
90	87	81	92	66	93	74	14	43	20
74	51	19	86	52	72	57	56	95	5
1	43	87	83	96	86	97	40	85	8
65	57	7	86	82	14	80	1	17	89
86	90	74	98	4	34	97	96	88	93
94	6	72	89	1	3	34	40	65	57
65	83	93	3	57	17	34	97	20	86
94	6	72	89	1	3	34	40	65	57

 

[arnelgp]

this is more random:

select id, [customer name] 
from table1 
order by RandomNumber([id], DMin("id","table1"), DMax("id", "table1"))

the function:

Public Function RandomNumber(anyField As Variant, Optional Lowest As Long = 1, Optional Highest As Long = 999999)
   Randomize (Timer)
   RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
End Function

 

[MajP]

this is more random:

Not really. Still has the same limitation in vb covariance.
This is better and has been mathematically proven if you really need it.

Option Explicit

Const seedOne As Integer = 200
Const seedTwo As Integer = 300
Const seedThree As Integer = 400
'ix,iy,iz are initially seeded, but are then recalculated each time a number is generated
'based on the number generated.  The initial seed value should be between 1 and 3000.
Public blnSeedSet As Boolean
Public ix As Integer
Public iy As Integer
Public iz As Integer
Public Sub subSetSeed()
  ix = 1 + Rnd() * 2999
  iy = 1 + Rnd() * 2999
  iz = 1 + Rnd() * 2999
End Sub
Public Sub subSetSeedRepeatable()
  ix = seedOne
  iy = seedTwo
  iz = seedThree
End Sub
Public Function fncBetterRnd(force As Variant, Optional blnRepeatable As Boolean) As Double
        If Not blnSeedSet Then
           If blnRepeatable Then
             Call subSetSeedRepeatable
           Else
             Call subSetSeed
           End If
           blnSeedSet = True
        End If
        Dim dx As Double
        Dim dy As Double
        Dim dz As Double
        Dim top As Double
        Dim c1 As Double
        Dim c2 As Double
        Dim c3 As Double
        Dim tmp As Long
        Dim l1 As Long
        Dim l2 As Long
        Dim l3 As Long
        Dim r1 As Long
        Dim r2 As Long
        Dim r3 As Long

'     Algorithm AS 183 Appl. Statist. (1982) vol.31, no.2
'    Returns a pseudo-random number rectangularly distributed
'    between 0 and 1.   The cycle length is 6.95E+12 (See page 123
'    of Applied Statistics (1984) vol.33), not as claimed in the
'    original article.
'    inputs are seed values IX,IY,IZ
'    output is pseudo random number between 0 and 1
'    which can obviously be converted to random integer
'     r=int((randu*N)+.5) etc where randu is 0,1 and N is maximum integer desired
'    IX, IY and IZ should be set to integer values between 1 and
'    30000 before the first entry.

         l1 = 171
         l2 = 172
         l3 = 170
         r1 = 30269
         r2 = 30307
         r3 = 30323
         c1 = CDbl(r1)
         c2 = CDbl(r2)
         c3 = CDbl(r3)
         ix = (l1 * ix) Mod r1
         iy = (l2 * iy) Mod r2
         iz = (l3 * iz) Mod r3
         dx = CDbl(ix)
         dy = CDbl(iy)
         dz = CDbl(iz)
        
' Generate random uniform number
          top = ((dx / c1) + (dy / c2) + (dz / c3))
          tmp = Int(top)
          fncBetterRnd = top - tmp
    End Function

 

[MajP]

Regardless my point is that

strSQL = "SELECT TOP " & Me.RandomNumberInput & " *" _
& "FROM tCustomers " _
& "ORDER BY Rand(CustomerId);"

Does not work because RAND is not a vba function it is RND

strSQL = "SELECT TOP " & Me.RandomNumberInput & " *" _
& "FROM tCustomers " _
& "ORDER BY [B]Rnd()[/B];"

Does not work because you get the same random number for every record

strSQL = "SELECT TOP " & Me.RandomNumberInput & " *" _
& "FROM tCustomers " _
& "ORDER BY [B]Rnd(1000)[/B];"

Still does not work because you get the same random number for every record

strSQL = "SELECT TOP " & Me.RandomNumberInput & " *" _
& "FROM tCustomers " _
& "ORDER BY Rnd(CustomerID);"
Me.RecordSource = strSQL

Still does not really work since you get the same sequence of numbers every time you open the db

This will work

Public Function MyRnd(Optional Seed As Variant = 1, Optional NewSeries As Boolean = True) As Double
  'You have to seed it with a unique value per record if used in a query
  If NewSeries Then Randomize
  MyRnd = Rnd(Seed)
End Function

Randomizing the seed with the Timer really does not add anything since you are already randomizing.

 

[arnelgp]

The first post is air code.
So ehere is the proof of the mathematicslly proven.

 

[MajP]

I need to go back and find the paper and see how dated it is, and if includes analysis post 2003. I do not remember. Pretty interesting because even with simple graphics you could see significant clustering in the VBA and Excel data. The co-variance matrices where also quite obvious but less intuitive. Prior to 2003 Excel and VBA had serious problems with the random number generator, after 2003 it was greatly improved. I will see if I can dig it up. For 99% of users it will probably be sufficient to get pseudo random variables.

 

[MajP]

And looking at this
https://support.microsoft.com/en-us/help/828795/description-of-the-rand-function-in-excel
It appears that the code I posted is an implementation of the Wichman and Hill algorithm which is now implemented in Excel and I assume vba. So there may not be any difference.