Another Random Question

[nancier]

I have a program that can do this but need to be able do this in access.

Lower Range = 1
Upper Range = 9999999

I would like the module to select a random seed number, user specified or from the system clock between -2147483647 and +2147483647. Then, using this seed number, I want to select a specified number(3 for instance) of random occurences in the 1 To 9999999 range.

Anyone know how to do this?

Thanks

 

[Craig0201]

http://www.exceltip.com/st/Return_random_numbers_using_VBA_in_Microsoft_Excel/531.html

Use the Excel functions.

 

[VBslammer]

I'm not exactly sure what you're trying to do, but you can make a function that finds a random number between 2 values and call it X number of times to get them:

Function GetRandomBetween(lngMin As Long, ByVal lngMax As Long) As Long
On Error GoTo ErrHandler

  Randomize Timer()
  
  GetRandomBetween = CLng((lngMax - lngMin + 1) * Rnd + lngMin)

ExitHere:
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

To get 3 randoms, call the function 3 times:

Sub Get3Randoms()
  Dim i As Integer
  
  For i = 1 To 3
    Debug.Print GetRandomBetween(1, 9999999)
  Next i
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[nancier]

That looks really great. I see you are getting the initial seed from the Timer. Is their a way to randomly choose the initial seed from between -2147483647 and +2147483647 and also print out what seed was used along with the 3 random numbers?

Thanks

 

[VBslammer]

You could add another method to seed the randomizer using your custom values. In order to deal with the negative value, I just look for a random between 1 and 2147483647, then randomly change its sign to negative.

Function GetRandomBetween(ByVal lngMin As Long, ByVal lngMax As Long) As Long
On Error GoTo ErrHandler
  
  GetRandomBetween = Fix((lngMax - lngMin + 1) * Rnd + lngMin)

ExitHere:
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

Function SeedRandomizer() As Long
  Dim lngSeed As Long
  Const MIN = 1
  Const MAX = 2147483647
  
  [green]'get a random seed value between min and max[/green]
  lngSeed = GetRandomBetween(MIN, MAX)
  
  [green]'get a random sign value[/green]
  If GetRandomBetween(1, 2) = 1 Then
    lngSeed = -lngSeed
  End If
  [green]'randomize using the new seed[/green]
  Randomize lngSeed
  
  [green]'return the seed value[/green]
  SeedRandomizer = lngSeed
End Function

After running the following test method 3 times:

Sub Get3Randoms()
  Dim i As Integer
  
  Debug.Print "Seed Value: " & SeedRandomizer
  
  For i = 1 To 3
    Debug.Print "Random " & i & ": " & GetRandomBetween(1, 9999999)
  Next i
End Sub

The output looks like:

[blue]
Seed Value: 1442084096
Random 1: 1234544
Random 2: 3774209
Random 3: 8168763
Seed Value: -2002501504
Random 1: 258781
Random 2: 3467245
Random 3: 6917411
Seed Value: 1801065472
Random 1: 4235364
Random 2: 7670915
Random 3: 6531131
[/blue]

VBSlammer

Unemployed in Houston, Texas

 

[nancier]

Wow, you're scary good!!!

Thanks so much

 

[nancier]

Hi, I did notice that the same numbers repeat if you close down access and start up again. I know there is a fix for that and I'm trying to remember it. Do you know it offhand? Thanks

 

[VBslammer]

I'd seed the randomizer using Timer() just before setting the custom seed value:

Function SeedRandomizer() As Long
  Dim lngSeed As Long
  Const MIN = 1
  Const MAX = 2147483647

  [green]'this should do it[/green]
  Randomize Timer()
  
  'get a random seed value between min and max
  lngSeed = GetRandomBetween(MIN, MAX)
  
  'get a random sign value
  If GetRandomBetween(1, 2) = 1 Then
    lngSeed = -lngSeed
  End If

  'randomize using the new seed
  Randomize lngSeed
  
  'return the seed value
  SeedRandomizer = lngSeed
End Function

VBSlammer

Unemployed in Houston, Texas

 

[nancier]

Looks like works. Great Job!! Thanks