Results 1 to 6 of 6
  1. #1
    Mhumlie is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    3

    Randomize does not appear to work. RND Function returning same integer repeatedly

    Using Office 365 Access and Windows 10. Wrote simple function to produce Random integer between 1 and 26:

    Function Call_Rnd()



    Randomize
    Call_Rnd = Int(26 * Rnd(6) + 1)
    End Function

    Call to function from query, each record returns exact same value. The integer is between 1 and 26 but for 200 record query, returns same integer 200 times. If I comment out "Randomize", same result with only Rnd.

    To me, it appears as if Randomize function is not working correctly.

    Has anyone else had this problem, or can you see anything I am doing incorrectly?

    Thank you

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Randomize is using system timer as seed. Must process so fast every record has same seed. try:

    Function Call_Rnd(intSeed)
    Randomize (intSeed)
    Call_Rnd = Int(26 * Rnd(6) + 1)
    End Function

    SELECT *, Call_Rnd([ID]) AS Num FROM table;

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Where does the Rnd(6) come from?
    The correct syntax is Int ((upperbound-lowerbound +1)*Rnd+lowerbound)

    So in your case that becomes Int(26*Rnd +1)
    Try this

    Code:
    Function Call_Rnd()
    
    Randomize
    Call_Rnd = Int(26 * Rnd + 1)
    End Function
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Tested. Still returns same value for every record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Mhumlie is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    3
    Than you to all responders:

    I did test to see if speed of processor was such that the Timer value did not change. This was not the case. Delaying the time through use of "Sleep()" function did not change result.

    I did find a solution.

    This function was being called by a query with 200 records. One value was displayed for each record. The function in the Query was: Call_Rnd() (No argument was sent to function and function did not require argument.). Apparently in this situation, Access only calls the function one time and then uses the returned value for every subsequent record. Discovered this by inserting a Msgbox in Function to see timer() value. Only one value was ever displayed. Re-ran the query using a dummy argument (I sent a value from the query as an ID) and the function worked exactly as I thought it should. Random values were created.

    I learned something. I did not know the query would only call the function one time if no argument was sent. To call it with every record, send argument, even if it is not used in calculation. Argument forces a call with every record.

    Thank you all for your help.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's a sample function to produce random numbers.
    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : randomNumber
    ' Author    : Jack
    ' Created   : 11/18/2010
    ' Purpose   : To Generate Random numbers between and including a range of numbers.
    'Lo and Hi are the lowest and highest random numbers you wish to generate.
    '
    'The Randomize keyword is critical to getting different results for each Access session.
    '=======================================================================================
    '---------------------------------------------------------------------------------------
    ' Last Modified:
    '
    ' Inputs: N/A
    ' Dependency: N/A
    '------------------------------------------------------------------------------
    '
    Function randomNumber(Lo As Long, Hi As Long) As Long
    10       On Error GoTo randomNumber_Error
    
    20    Randomize
    30    randomNumber = Int((Hi - Lo + 1) * Rnd + Lo)
    
    40       On Error GoTo 0
    randomNumber_Exit:
    50       Exit Function
    
    randomNumber_Error:
    
    60        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure randomNumber of Module AccessMonster"
    70        GoTo randomNumber_Exit
               
    End Function
    And here is a test routine to call the function.
    Code:
    ' Procedure : mytestOfRandomNumber
    ' Author    : Jack
    ' Date      : 11/18/2010
    ' Purpose   : Create 10 random numbers between a and z
    '---------------------------------------------------------------------------------------
    '
    Sub mytestOfRandomNumber()
              Dim A As Long
              Dim Z As Long
              Dim i As Integer
    10        A = 1
    20        Z = 26
    30        For i = 1 To 15
    40            Debug.Print i & "   " & randomNumber(A, Z)
    50        Next i
    
    60       On Error GoTo 0
    70       Exit Sub
    
    mytestOfRandomNumber_Error:
    
    80        MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure mytestOfRandomNumber of Module AccessMonster"
    End Sub
    Results of 3 sample runs:

    1 4
    2 22
    3 2
    4 15
    5 15
    6 3
    7 7
    8 13
    9 1
    10 11
    11 1
    12 13
    13 22
    14 9
    15 22

    1 22
    2 19
    3 2
    4 20
    5 5
    6 23
    7 1
    8 4
    9 2
    10 8
    11 16
    12 24
    13 2
    14 2
    15 7

    1 5
    2 2
    3 17
    4 18
    5 8
    6 11
    7 21
    8 25
    9 19
    10 9
    11 7
    12 7
    13 6
    14 7
    15 26

    Hope it's helpful.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 11-30-2016, 05:03 PM
  2. VBA Lookup Function to Access Returning #value
    By troygeri in forum Queries
    Replies: 2
    Last Post: 05-26-2013, 08:50 PM
  3. Function - Returning a Value
    By dreamnauta in forum Programming
    Replies: 3
    Last Post: 01-05-2012, 03:37 PM
  4. Adding column as INTEGER makes it a long integer?
    By luckycharms in forum Programming
    Replies: 2
    Last Post: 10-20-2010, 02:47 PM
  5. Calling a function and returning a value
    By 3dmgirl in forum Programming
    Replies: 0
    Last Post: 04-23-2007, 02:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums