Access rnd unrecognized function.

Try:

VBA.Rnd

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

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?

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

Go to the VB Editor, and see if any references are listed as missing.

Is this not the same Q as:

https://www.experts-exchange.com/questions/22870529/MS-Access-RND-causes-unknown-function-error.html?cid=238&anchorAnswerId=20016827#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

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

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)

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)

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

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

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();

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

Bingo!

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.

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

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

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

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.

OLE Automation library has StdFont and StdPicture classes.  Not really associated with automation of external COM libraries.

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

Unless you were using the StdFont and/or StdPicture classes, no, it's not necessary.

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

mx

Ok, I have all of the references as suggested.

Also - Appreciate all of the conversation!

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

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

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

Troyo