Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2006
    Posts
    12

    Question Unanswered: Getting a Random record - SOLVED!

    I have tried to find it on the forum but cannot find a solution...

    I have a MS-Access db with a table Members... One of the fields is calles Mailing... I now want to draw randomly one person who is a member of this Mailing (=true).
    How can I accomplish this?

    The table Members looks as followed:
    MemberID (long int/prim key)
    FName (text)
    LName (text)
    Email (text)
    Mailing (Boolean)

    I am not a coding guru, so please have patience with me on the coding part

    Anyone who can help me out?
    Last edited by flipandboef; 04-16-07 at 13:47. Reason: Solved

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by vbHelp
    The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain.
    Try using this, though I don't know entirely how random it is.
    Me.Geek = True

  3. #3
    Join Date
    Dec 2006
    Posts
    12
    hmmmm don't really get that....

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    bearing in mind that you cannot truly get random numbers on a standard PC, what you could do is write a function that makes use of the rnd function

    first query your membership dataset
    then find the number of possible membership records you want to select from
    then generate a random number in the range 1...no qualifying members
    then extract the details of nth member (matching the random number) in your list.

    of course if you used something like MySQL it would be a breeze as it supports random record selection... wonder if Access does......?

    have a look at the rnd function in the Access help
    the order by desc/asc term in SQL, the limit / top term may be useful as well.

    you could extend this function to say include or exclude people who have been selected before
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2006
    Posts
    12
    Ok found a solution for in a query:

    In the SQL -->

    SELECT TOP 2 MEMBERS.*
    FROM MEMBERS
    WHERE ((([Members]![Mailing])=True))
    ORDER BY Rnd(IsNull(MEMBERS.MemberID)*0+1);

    Members = table
    Mailing = field that needs the condition
    top 2 = View 2 random records.. Change this to get more or less...

    Hope it helps for someone else ;o)

Posting Permissions

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