• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

How do I create a query in MS Access 2007 to select a random Value?

I have tried the below but am not getting anywhere.  Last week the below formula worked,b ut now it doesn't.  Is there a better way to random a formula for a MS Access Query to pull for me a random value?

Int ((6 - 1 + 1) * Rnd + 1)

would return a random number between 1 and 6

Int ((200 - 150 + 1) * Rnd + 150)

would return a random number between 150 and 200

Int ((999 - 100 + 1) * Rnd + 100)

would return a random number between 100 and 999
  • 2
  • 2
1 Solution
Patrick MatthewsCommented:
Please explain what you mean by "doesn't work".
Wraith300Author Commented:
When I attempt to run it, I receive  compile error message:  "Compile error in query expression"  then it shows whatever the criteria in the query was ex.  1: Int((7-2+1)*Rnd()+20)
Patrick MatthewsCommented:
What was the full SQL statement?
Try this:

for table a (f1, ...)

SELECT a.f1, rndNo(7,1,[f1]) AS randomNo

f1 is added to force calculation for each record.

Where rndNo is a unction in a module:

 Function rndNo(up As Integer, lo As Integer, f1 As Integer) As Integer
    Dim x As Integer
    rndNo = Int((up - lo + 1) * Rnd + lo)
 End Function
Wraith300Author Commented:
After additional testing, it turned out the actual database had an issue that caused formulas to drop the last characters.  I created a new database, used the same formula and everything is working again.  Thanks for the help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now