Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Return New Random Value for Each Record In Query

    Hi all,

    Using Access 2010.
    I would like a new rand o value for each record in my query. Currently, it is just returning the same value for each record.

    I created this Public Function:
    Code:
    Public Function GetRandomValue() As Double
    
        Dim dblRandom As Double
        
            dblRandom = (1000000 - 500000 + 1) * Rnd() + 500000
            GetRandomValue = dblRandom
            
    End Function
    But it retuns the same value for every record in the query results: 852,774.46....

    How do I get a new value for every record?

    Thx,
    w

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you don't pass any value from the data set processed by the query to the function, this function will be called only once. Try to change the declaration of the function to accept a (dummy) parameter, like this:
    Code:
    Public Function GetRandomValue(Byval Column As Variant) As Double
    Then pass the value of any non-constant value when calling the function from the query (the Identity column, if any, would be a good candidate for such a value).
    Have a nice day!

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sinndho,

    I added to the Function:
    Code:
    Public Function GetRandomValue(intValue As Integer) As Double
    
        Dim dblRandom As Double
        
            dblRandom = (1000000 - 500000 + 1) * Rnd() + 500000
            GetRandomValue = dblRandom
            
    End Function
    And passed the RecorndNumber to the Function (Dummy)

    Code:
    Expr1: GetRandomValue(DLookUp("RecordNumber","tblLocations"))
    But I'm still getting the same random value on every record. At least the value changes to a new value with every refresh of the query. But all 600 records have the same value.

    thx
    w

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the query, simply use:
    Code:
    GetRandomValue(RecordNumber)
    The DLookUp() function always returns the same value, hence the function is only called once.
    Have a nice day!

Posting Permissions

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