Randomize Not Working (3 Viewers)

[MajP]

I have the following procedure to assign random order to records and it works.

Public Sub ShuffleTileOrder()
  'Update the table at beginning of game
  Dim i As Integer
  Dim rs As DAO.Recordset
  Dim strSql As String
 
  Randomize (CDbl(Now))
 
  strSql = "Select rnd([Letterid]) as sort, letter, Letterorder from tblGameLetters order by rnd([Letterid])"
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    i = i + 1
    rs.Edit
    rs!Letterorder = i
    rs.Update
    rs.MoveNext
  Loop
End Sub

The first time I Run the method I get this order
A
I
T
B
L
E
Y
E
?
P

Second time I run it I get this order
E
E
U
N
O
E
R
F
I
N

Now if I close the DB and reopen it and run the method
1st Time
Letter
A
I
T
B
L
E
Y
E
?
P

Second Time
Letter
E
E
U
N
O
E
R
F
I
N

This is exactly what I would expect if I did not Include Randomize. I would get a random patterns each time, but every time I open the db I would get the same order of random patterns. However I included Randomize. I would expect different random patterns.

Any Idea or this some kind of bug?

 

[GPGeorge]

I think you have to start with a different seed value each time, e.g. maybe something based on Now(). But maybe that's just some random misremembering thing I picked up.

 

[June7]

As far as I can tell, Randomize doesn't do anything for the recordset. It's used if you want to generate a random number in VBA. https://learn.microsoft.com/en-us/o...rence/user-interface-help/randomize-statement

Dim MyValue
Randomize ' Initialize random-number generator.
MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.

In SQL statement, provide the "seed" value in the Rnd() function. https://stackoverflow.com/questions/35454825/random-sorting-query-access
strSql = "SELECT LetterID, letter, Letterorder FROM tblGameLetters ORDER BY Rnd(-Timer() * [LetterID])"

 

[CJ_London]

You do need to start with a different seed

 

[MajP]

You do need to start with a different seed

No you do not (or at least you should not according to MS since it uses the timer)

Randomize uses number to initialize the Rnd function's random-number generator, giving it a new seed value. If you omit number, the value returned by the system timer is used as the new seed value

 

[MajP]

Here is the thing if I modify the code as other suggest still does nothing. Each time I open the database I get the same order of patters.

Public Sub ShuffleTileOrder()
  'Update the table at beginning of game
  Dim I As Integer
  Dim rs As DAO.Recordset
  Dim strSql As String
 
  Randomize (cdbl(Now))
 
   strSql = "Select rnd([Letterid]*cdbl(NOW) ) as sort, letter, Letterorder from tblGameLetters order by rnd([Letterid]* cdbl(Now))"
 ...
 
End Sub

 

[sonic8]

Randomize (cdbl(Now))

I believe (not verified) Randomize only uses the integer part of the numeric argument as seed. So, during any day you will get the same sequences but other sequences the next day.

Either do not supply any seed at all to let it use the system timer or make sure the integer part of the seed is different each time. E.g.,:
Randomize cdbl(Now)*1000000

 

[June7]

My suggested modification works for me. Don't use Rnd() in the SELECT clause, only in ORDER BY.

I tested the SQL in query object as well as VBA recordset.

 

[MajP]

I believe (not verified) Randomize only uses the integer part of the numeric argument as seed. So, during any day you will get the same sequences but other sequences the next day.

Either do not supply any seed at all to let it use the system timer or make sure the integer part of the seed is different each time. E.g.,:
Randomize cdbl(Now)*1000000

Thanks. That seems to be the case that it only uses the Integer. Nothing in the literature on that. But that appears to be an issue with the RND seed as well.

My suggested modification works for me

Thanks. Sorry I missed the suggestion. That does work.

As far as I can tell this is specifically an issue with a recordset and this is not an issue with a query or code. Or at least I cannot replicate this same problem with a query or code. If I randomize when opening a db the query or code gives different patterns.

So using your suggestion you have to individually seed the RND with a value that changes in the integer portion. This explains why Now does not work but Timer does. Also this approach simply disregards what Randomize is supposed to do and you can drop from the code.

I found you can also randomize at every call to the function and that will work too.

Public Sub ShuffleTileOrder()
  'Update the table at beginning of game
  Dim i As Integer
  Dim rs As DAO.Recordset
  Dim strSql As String

  strSql = "Select letter, Letterorder from tblGameLetters order by myrnd([Letterid])"
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
     i = i + 1
     rs.Edit
     rs!Letterorder = i
     rs.Update
     rs.MoveNext
  Loop
End Sub

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

Or you I could in this case simply done this in code since I save it to the table and this is not an issue when assigning through code.

 

[The_Doc_Man]

This is a dumb question, maybe, but when you use RND in an SQL string, isn't it in the context of the SQL engine, whereas RANDOMIZE as you showed it is in the context of the GUI? I don't know that the two contexts can see each other that well. Or does SQL have to "backtrack" to the GUI context for VBA functions?

 

[MajP]

This is a dumb question, maybe, but when you use RND in an SQL string, isn't it in the context of the SQL engine, whereas RANDOMIZE as you showed it is in the context of the GUI? I don't know that the two contexts can see each other that well. Or does SQL have to "backtrack" to the GUI context for VBA functions?

That is probably something to do with it, or at least helps explain some of it.

 

[The_Doc_Man]

I looked it up in the VBA language standard. The rule for RANDOMIZE() is that you can leave it with no argument. However, IF you supply a seed number, it must comply with the standards for variable coercion to DOUBLE data type. DATE would of course make that, as would the CDBL() function you showed earlier. When offered without an argument, RANDOMIZE uses the TIMER function, which returns a SINGLE.

 

[AHeyne]

I agree with @The_Doc_Man

Apart from that, the pure VBA solution is not really random.

As far as I know, the 'Rnd' procedure always runs through the exact same (very, very, long) number line, which is fixed internally.
The 'Randomize' procedure merely ensures that the current pointer, which indicates the position where 'Rnd' takes the next number, is moved to a different position on the number line.

To get really random values you can use the 'BCrypt' API procedures.

I use this procedure for this:

Private Declare PtrSafe Function BCryptOpenAlgorithmProvider Lib "bcrypt.dll" (ByRef hAlgorithm As LongPtr, ByVal pszAlgID As LongPtr, ByVal pszImplementation As LongPtr, ByVal dwFlags As Long) As Long
Private Declare PtrSafe Function BCryptGenRandom Lib "bcrypt.dll" (ByVal hAlgorithm As LongPtr, ByVal pbBuffer As LongPtr, ByVal cbBuffer As Long, ByVal dwFlags As Long) As Long
Private Declare PtrSafe Function BCryptCloseAlgorithmProvider Lib "bcrypt.dll" (ByVal hAlgorithm As LongPtr, ByVal dwFlags As Long) As Long

Public Function GetRandomValueBCrypt(ByVal minimumValue As Long, ByVal maximumValue As Long) As Long
    '// RNG: BCRYPT_RNG_ALGORITHM - The random-number generator algorithm.
    Dim cngAlgorithmIdentifier As String
    cngAlgorithmIdentifier = "RNG" & vbNullChar

    Dim algorithmHandle As LongPtr
    BCryptOpenAlgorithmProvider algorithmHandle, StrPtr(cngAlgorithmIdentifier), 0, 0

    Dim bitTemplate As Long
    Dim xIndex As Long
    Do While bitTemplate < maximumValue - minimumValue
        bitTemplate = bitTemplate + 2# ^ xIndex
        xIndex = xIndex + 1
    Loop

    Dim randomNumber As Long
    Do
        BCryptGenRandom algorithmHandle, VarPtr(randomNumber), LenB(randomNumber), 0

        randomNumber = randomNumber And bitTemplate
    '// Refresh if larger than desired range (should happen less than 50% of times)
    Loop While randomNumber > (maximumValue - minimumValue)

    GetRandomValueBCrypt = randomNumber + minimumValue

    BCryptCloseAlgorithmProvider algorithmHandle, 0
End Function

 

[The_Doc_Man]

ALL computer random number generators are cyclic in the long run as a consequence of having finite data sizes leading to truncation of the sequence, whatever it is. The only thing you do by using some particular "random number" generator is you lengthen the cycle. That doesn't mean the numbers aren't any good for short-term randomness.

 

[AHeyne]

I just feel comfortable with the use of the 'Cryptography API: Next Generation' in contrast to 'Rnd/Randomize' and don't have to worry about whether the random numbers are ‘sufficiently’ random.

 

[MajP]

I just feel comfortable with the use of the 'Cryptography API: Next Generation' in contrast to 'Rnd/Randomize' and don't have to worry about whether the random numbers are ‘sufficiently’ random.

It is still a psuedo random generator and not a true random number generator, but it is pretty darn good. It complies with the NIST SP800-90 standard, specifically the CTR_DRBG portion of that standard. Had to look that up but the publication: contains the specification for three allegedly cryptographically secure pseudorandom number generators for use in cryptography: Hash DRBG (based on hash functions), HMAC DRBG (based on HMAC), and CTR DRBG (based on block ciphers in counter mode). Seems like a pretty high bar.

 

[AccessBlaster]

I've used this a few times and it always opens to a new random selection.

A sample I used here > https://www.access-programmers.co.uk/forums/threads/randoms-are-not-random.198986/post-1001997

 

[The_Doc_Man]

don't have to worry about whether the random numbers are ‘sufficiently’ random.

I understand your preference. Chacun a son gut, as the French would say.

However, "sufficiently random" simply means the number of calls to the generator function is less than the length of its cycle. If all I need are, say, 52 random numbers because I am building a randomized card shuffler, that is far fewer numbers than the cycle of the RND function and so RND is "sufficiently random." Attempting to determine a numeric "randomness" score is actually a contradiction akin to computing the number of entries in an infinite series. Sort of like trying to compute the entropy of a collection of molecules in a room-temperature sealed plastic container. You can do such a thing, but when you've done it, what did you get out of it (besides a headache or sore fingers from tapping calculator keys so often)?

If you've got the means and the inclination, I see no issue in using whatever you WANT to use. Just commenting on the nature of randomness.

 

[MajP]

I've used this a few times and it always opens to a new random selection

Yes, I believe @The_Doc_Man explains why this is.

Although I randomize in vba before calling the recordset the RND function is executing in a SQL environment and not in VBA. Therefore any RANDOMIZE done in VBA has no effect to a function executing in the SQL environment. If you call an external VBA function then the randomize is affected since the function is running in VBA. In fact you do not need to call Randomize in the function, but can do it just once when the db opens.

 

[MajP]

I am building a randomized card shuffler, that is far fewer numbers than the cycle of the RND function and so RND is "sufficiently random." Attempting to determine a numeric "randomness" score is actually a contradiction akin to computing the number of entries in an infinite series.

That is not true. The cycle length is only a small part of it. It is the internal patterns and correlation has a bigger effect on determining randomness. There are plenty of mathematical tests for randomness. Some generators are far superior to others

Statistical randomness - Wikipedia

en.wikipedia.org

There are simple plots of two random generators that will show differences. You will see patterns and groups. Obviously the one on the right is a better generator.

If you are building your own card shuffler. No big deal. Guarantee the ones in a real poker machine use a far superior generator.