We help IT Professionals succeed at work.

Access rnd unrecognized function.

424 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.
Comment
Watch Question

Commented:
Try:

VBA.Rnd

It's possible you have something else conflicting with the Rnd name.
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
What are you doing?

Are you saying that the VBA Rnd function always produces the same number instead of an unpredictable one?
How are you calling it?
CERTIFIED EXPERT
Top Expert 2010

Commented:
toverholt said:
>> Is there something I am missing?  

Go to the VB Editor, and see if any references are listed as missing.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Is this not the same Q as:

https://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

Author

Commented:
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

Author

Commented:
Closing this question
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)

Commented:
It's the Sql function Rnd, not the VBA function... Don't use the VBA prefix.  

But what exactly do you want to achieve?
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)

Commented:
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]);

Author

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

Author

Commented:
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.
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();
CERTIFIED EXPERT
Top Expert 2010

Commented:
Angelp1ay said:
>>I think you are misusing the function. I don't think Rnd takes arguements.

Bingo!

Commented:
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.

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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.

Commented:
OLE Automation library has StdFont and StdPicture classes.  Not really associated with automation of external COM libraries.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Commented:
Unless you were using the StdFont and/or StdPicture classes, no, it's not necessary.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Gee ... do you think M$ could have picked a better name for the Reference ?

mx

Author

Commented:
Ok, I have all of the references as suggested.

Also - Appreciate all of the conversation!
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

Commented:
As simple as that sounds...importing all objects into a new DB makes the RND function work.

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

Troyo

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.