Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Random numbers (Access 2000)

    I need to set up a password field in a user table, and I thought of using an update query to fill it with something like the user's name with a random number appended. I tried using the following code that I found, but it generates the same random number for each record! How can I get a different random number for each record?
    Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Random numbers (Access 2000)

    Does your table contain a number field? If so, use it as argument to Rnd:

    Int ((upperbound - lowerbound + 1) * Rnd([FieldName]) + lowerbound)

    where FieldName is the name of the number field.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random numbers (Access 2000)

    If your table doesn't contain a number field, try the following...
    <UL><LI>In a module (create a new one if necessary), copy the following:
    Function Random(Rubbish As Variant)
    upperbound = 100
    lowerbound = 1
    Random = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
    End Function

    <LI>(Obviously change the upperbound and lowerbound values to ones you want to use - I've used 100 and 1)
    <LI>In your query, use Random([Staff Name]) to generate your random numbers (where [Staff Name] is the name of any field in your table.)[/list]
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Random numbers (Access 2000)

    Thanks for your reply. I tried your suggestion, but I got an error saying that the update failed because of a type conversion error. I used a number type field for the [FieldName].

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Random numbers (Access 2000)

    Could you do the following?
    - Open your query in design view.
    - Select View | SQL
    - Copy the text to the clipboard (Ctrl+C)
    - Paste it into a reply
    That will allow Loungers to inspect the "code" of the query.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Random numbers (Access 2000)

    Here is the SQL code.
    UPDATE User SET User.Password = Int(("upperbound - lowerbound"+1)*Rnd([ClientId])+"lowerbound");

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Random numbers (Access 2000)

    As you can see from the quotes, upperbound and lowerbound are seen as text strings, not as numbers. You must replace them with number values and remove the quotes, e.g. if the lower bound is 30 and the upper bound is 100:

    UPDATE User SET User.Password = Int((100-30+1)*Rnd([ClientId])+30);

    BTW, this will set the password to a single number, not the username plus a number. If you want to include the username, you can use the GetNetUser function from <post#= 446010>post 446010</post#>. You must copy the code into a module; you can then use

    UPDATE User SET User.Password = GetNetUser() & Int((100-30+1)*Rnd([ClientId])+30);

    Not: I have assumed that you haven't applied user-level security; if you had, you wouldn't have a need for a table with passwords.

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Random numbers (Access 2000)

    Works fine now. Thanks for your patient assistance.

Posting Permissions

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