?
Solved

Access rnd unrecognized function.

Posted on 2007-10-04
28
Medium Priority
?
337 Views
Last Modified: 2011-10-03
Access will no recognized the rnd function for me.  Is there something I am missing?  I need to order a set of records, then choose the top few.
0
Comment
Question by:toverholt
  • 8
  • 7
  • 6
  • +3
28 Comments
 
LVL 38

Expert Comment

by:PaulHews
ID: 20017611
Try:

VBA.Rnd

It's possible you have something else conflicting with the Rnd name.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20017621
What are you doing?

Are you saying that the VBA Rnd function always produces the same number instead of an unpredictable one?
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20017623
How are you calling it?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20017648
toverholt said:
>> Is there something I am missing?  

Go to the VB Editor, and see if any references are listed as missing.
0
 
LVL 75
ID: 20017801
Is this not the same Q as:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22870529.html?cid=238#a20016827

If so, please go back and close out (or delete) that Q so that Experts are not wasting time on the same Q in two places -  with a different set of Experts in each Q.

mx
0
 

Author Comment

by:toverholt
ID: 20017830
In this query:

SELECT tblData.Name, tblConList.CONID, tblConList.DateIn, tblConList.DateOut, tblData.SSN, tblData.Co, tblCo.Company, VBA.rnd([tblData].[ID]) AS Expr1
FROM (tblConList INNER JOIN tblData ON tblConList.EMPID = tblData.ID) INNER JOIN tblCo ON tblData.Co = tblCo.ID
WHERE (((tblConList.DateOut) Is Null));

It gives me an error of unknown function VBA.RND

0
 

Author Comment

by:toverholt
ID: 20017843
Closing this question
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20017852
I think you are misusing the function. I don't think Rnd takes arguements.

http://www.techonthenet.com/access/functions/numeric/rnd.php

Try forming your expression like this:
    Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 20017935
It's the Sql function Rnd, not the VBA function... Don't use the VBA prefix.  

But what exactly do you want to achieve?
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20017968
In MSSQL you could use:
    Select * from table
    ORDER BY NEWID()
...apparently.

Since you are using Access I think you can also use what I posted above:

http://www.techonthenet.com/access/functions/numeric/rnd.php

> Try forming your expression like this:
>     Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 20017982
To put them in random order and select top 5 should be something like this:

SELECT TOP 5 tblData.Name, tblConList.CONID, tblConList.DateIn, tblConList.DateOut, tblData.SSN, tblData.Co, tblCo.Company, [tblData].[ID] As DataID
FROM (tblConList INNER JOIN tblData ON tblConList.EMPID = tblData.ID) INNER JOIN tblCo ON tblData.Co = tblCo.ID
WHERE (((tblConList.DateOut) Is Null))
ORDER BY Rnd([tblData].[ID]);
0
 

Author Comment

by:toverholt
ID: 20018082
That still gives me the Unknown Function name in query expression 'Rnd([TblData].[ID])'

0
 

Author Comment

by:toverholt
ID: 20018101
In response to ...what are you trying to accomplish...

I just need to randomly order a query or table.  I have accomplished the same task, but for some reason I cannot make it happen now.

It appears that the rnd function is not recognized, and I have the reference to DAO.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20018138
Are you using MSSQL? Try this:

    SELECT TOP 5 tblData.Name, tblConList.CONID, tblConList.DateIn, tblConList.DateOut, tblData.SSN, tblData.Co, tblCo.Company, [tblData].[ID] As DataID
    FROM (tblConList INNER JOIN tblData ON tblConList.EMPID = tblData.ID) INNER JOIN tblCo ON tblData.Co = tblCo.ID
    WHERE (((tblConList.DateOut) Is Null))
    ORDER BY NewID();
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20018157
Angelp1ay said:
>>I think you are misusing the function. I don't think Rnd takes arguements.

Bingo!
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 20018227
Do you have the Visual Basic for Applications reference?

Check the references against the list here:
References that you must set when you use Microsoft Office Access 2003
http://support.microsoft.com/kb/825796/

Note that if you have a different version of Access, there are links to the same article for different versions at the top of the page.
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 20018276
For those who say he's misusing the function, that is incorrect:
How to find N records in random order in Access 2002
http://support.microsoft.com/kb/287682
0
 
LVL 75
ID: 20018296
What version of Access are you using here ?

Also ... the only *required* references - for A2003 are:

If using DAO Only

"      Visual Basic for Applications
"      Microsoft Access 11.0 Object Library
"      Microsoft DAO 3.6 Object Library

If using ADO Only:
"      Visual Basic for Applications
"      Microsoft Access 11.0 Object Library
"      OLE Automation
"      Microsoft ActiveX Data Objects 2.5 Library

If using DAO and ADO:

"      Visual Basic for Applications
"      Microsoft Access 11.0 Object Library
"      OLE Automation
"      Microsoft ActiveX Data Objects 2.5 Library
"      Microsoft DAO 3.6 Object Library
0
 
LVL 75
ID: 20018383
Longshot:

When the Replace function was added to Access in either 97 or 2000 (don't remember) ... it did *not* work when used in a query ... only in vba code. The error message was the same.  That was fixed in either 2000 or 2002 - FORTUNATELY.

I believe there were 2-3 other new functions with this issue - but, I do not recall what they were.

just an fyi ...

mx
0
 
LVL 75
ID: 20018387
Correct to above post:

If using DAO and ADO:

"      Visual Basic for Applications
"      Microsoft Access 11.0 Object Library
"      Microsoft ActiveX Data Objects 2.5 Library
"      Microsoft DAO 3.6 Object Library

Note    OLE Automation is only required in any case if you are using Automation for something.
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 20018493
OLE Automation library has StdFont and StdPicture classes.  Not really associated with automation of external COM libraries.
0
 
LVL 75
ID: 20018573
However, still it's not required.  It's there by default.  I drop it out unless specifically needed.

Paul ... are you saying that if ... say you opened Word/Excel via 'Automation', that would not be necessary?

mx
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 20018779
Unless you were using the StdFont and/or StdPicture classes, no, it's not necessary.
0
 
LVL 75
ID: 20018789
Gee ... do you think M$ could have picked a better name for the Reference ?

mx
0
 

Author Comment

by:toverholt
ID: 20021291
Ok, I have all of the references as suggested.

Also - Appreciate all of the conversation!
0
 
LVL 38

Accepted Solution

by:
PaulHews earned 2000 total points
ID: 20021593
You might try copying the database objects to a new Access database file.
0
 

Author Comment

by:toverholt
ID: 20021678
That sounds like a good idea!  I also originally named the DB RND, so I changed that but it did not seem to help.  I will import everything to a new file.

I will report out as well.

Troy
0
 

Author Comment

by:toverholt
ID: 20022508
As simple as that sounds...importing all objects into a new DB makes the RND function work.

Thanks to everyone for your help!!!!!!!

Troyo
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

807 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