Solved

rand() in access 2000 ?

Posted on 2003-11-03
2
1,154 Views
Last Modified: 2006-11-17
hi!

i have written a program using mysql.
in a query i´m using order by rand().

now i have to switch to access. but it seems that
there is no rand() function.

how can i handle this?
0
Comment
Question by:StoreMaster
2 Comments
 
LVL 23

Accepted Solution

by:
heer2351 earned 50 total points
ID: 9669772
The msaccess equivalent for rand is rnd
0
 
LVL 77

Expert Comment

by:peter57r
ID: 9669830
There is a rnd() function in Access; but you can't use it in the way you are trying to.

To generate a random field in a query you need to force Access to think that the random number is dependent on a field in the record or else the rnd() value is just calculated once at the start of the query and that value used on all records.

So you can include rnd([anyfield]) and this produces a different result for each record.

You can sort on this value in a query.  Unfortunately, by the time the data is displayed, the random function has triggered again and the numbers have changed so that they appear not to be in order.   If you want to retain the random number sequence you must first write the value to a new field in an update or make-table query and then use another query to present the sorted data.

Pete


0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now