Solved

rand() in access 2000 ?

Posted on 2003-11-03
2
1,142 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
Comment Utility
The msaccess equivalent for rand is rnd
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

14 Experts available now in Live!

Get 1:1 Help Now