Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Random Numbers (Office XP)

    Hi,
    I need to add a random number, via an update query to a data field in a table. I am currently exporting the data to excel, creating an random number field then ranking the data by the random number generated. I have tried experimenting with the RND function in Access but have so far failed.

    I need to rank the data only once so it dosn't matter if after running the update query the number created changes.

    Is there a way to do this??????

    Thanks...Peter

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Random Numbers (Office XP)

    What sort of problem are you having with the RND function? We use it regularly to do things and it works, so are you getting error messages or the same number all the time or what?
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Random Numbers (Office XP)

    I'll bet you tried to use the Rnd() function in a query, perhaps in an update query in which this was the "Update To" amount. The problem is that if Access sees a function with no variables, it will only call it once and then remember the value and use it for all the other times.

    To get around this, instead of using just Rnd(), you need to use Rnd(somevariable). If you have an ID field for each record, then use that.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Numbers (Office XP)

    Hi, you are exactly right, I was always getting the same number - 1. I tried adding in the id (an auto number field) as suggested, however I now get either a 1 or a 0. Am I missing something??
    I put Rnd(drawdata!id) in the update to field in the query design grid. Drawdata is the table name, id is the autonumber field name.

    Cheers....Peter

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Random Numbers (Office XP)

    what's the data type of the field you're updating? if it's an integer, try multiplying the Rnd() result by 100 or 1000.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Numbers (Office XP)

    Brilliant!!

    That solved it. Thanks very much.

    Regards..Peter

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Random Numbers (Office XP)

    Peter,

    As Charlotte mentioned, you can multiple by some number (such as 100) to get a better number. You need to understand what Rnd() does; it returns a decimal number (with Single datatype) that is >=0 but < 1. Multiplying by some number (like 100), then moving it to an integer field essentially gives you the first 3 significant digits. Therefore, unless you multiply by some very large number (like 1000000), it is likely that you will get some duplicates (just in case you were trying to use it erroneously to create a unique number for each record).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Numbers (Office XP)

    Thanks Mark. I am using the random number to select winners for a competition, so the advice on using the large multiplier number is a good thought.

    Regards...Peter

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Random Numbers (Office XP)

    When using update query to update a random number field using this expression:
    <pre>Int(Rnd([EmployeeID])*1000)</pre>

    it worked OK, but when there were usually several duplicate RandomID numbers (in a table with appx 100 records). Increasing 1000 to 10000 reduced the number of duplicates significantly, but some duplicates still sometimes showed up after running update query.

    It looks like you solved this problem, but if you want to avoid any possible duplicates, the sample code below will update a "RandomID" field (should be Integer or Long) so that each record in table has a unique RandomID number, ranging from 1 to total number of records. The sub takes two arguments: The name of table to be updated, and the name of the RandomID field:
    <pre>Public Sub RandomIDUpdate(strTbl As String, _
    strFld As String)
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strMsg As String
    Dim intLower As Integer
    Dim intUpper As Integer
    Dim n As Integer

    Set db = CurrentDb
    Randomize ' Initialize random-number generator.
    strSQL = "UPDATE " & strTbl & " SET [" & strFld & "]=0;"
    db.Execute strSQL
    strSQL = "SELECT * FROM " & strTbl & " WHERE [" & strFld & "]=0;"
    Set rst = db.OpenRecordset(strSQL)

    With rst
    .MoveLast
    .MoveFirst
    intLower = 0
    intUpper = .RecordCount - 1
    For n = 1 To .RecordCount
    .MoveFirst
    .Move Int((intUpper - intLower + 1) * Rnd + intLower)
    .Edit
    .Fields(strFld) = n
    .Update
    .Requery
    intUpper = intUpper - 1
    Next
    .Close
    End With

    strMsg = "New random number sequence has been generated for " & _
    strTbl & " table."
    MsgBox strMsg, vbInformation, "RANDOM ID NUMBERS UPDATED"

    Exit_Sub:
    Set db = Nothing
    Set rst = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err
    Case 3020, 3021 '3021 = No current record
    '3020 = Update or Cancel Update w/o AddNew or Edit
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbInformation, "RANDOM NUMBER UPDATE ERROR"
    End Select
    Resume Exit_Sub
    End Sub</pre>

    NOTE: Code was a little buggy in that when testing w/o error handling enabled, sometimes got Error 3021 (no current record) or, less frequently, Error 3020. The Resume Next statement allows sub to continue till all records are updated in this event. Error tended to occur only in larger table (appx 1000 records), in smaller table (100 records) did not tend to occur.

    This may be helpful if for some reason you need a table sorted in unique random number sequence without any duplicates.

    HTH

  10. #10
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Numbers (Office XP)

    Outstanding!!

    Thanks for that.

    Regards...Peter

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Random Numbers (Office XP)

    One additional note, if the number in the RandomID field does not have to be any kind of meaningful number and is only going to be used to sort table in random order, then you don't need to convert to Integer when using RND function in update query. The update query SQL would look like this:
    <pre>UPDATE Test SET Test.RandomID = Rnd([ID]);</pre>

    where [ID] is an AutoNumber field in table named Test. RandomID field should be defined as Number - Single, not Long or Integer, since RND function returns a Single data type. If this method used it would be very unlikely to end up with duplicate value in RandomID field.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •