[Microsoft][ODBC Microsoft Access Driver] Syntax Error in ORDER BY clause

#1 (**permalink**) 11-Nov-2005, 20:09 PM

Here is the statement...

SELECT *, rand() as random_number
FROM image_img
ORDER BY random_number DESC LIMIT 1

I suspect that the rand() is not supported in MS Access. I am not sure what
syntex to put in it's place...

Can anybody tell me what I have done wrong?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/7173-microsoft-odbc-microsoft-access-driver-syntax.html

*

#2 (**permalink**) 11-Nov-2005, 20:09 PM

Marc

If I were testing this SQL, I would first drop the ORDER BY clause and see
what Access returns for the rest.

What is the purpose of the "LIMIT 1" clause?

Jeff Boyce

"Marc Everlove" wrote in message
news:%23oz7mkl5FHA.2364@TK2MSFTNGP12.phx.gbl...
> Here is the statement...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
>
> SELECT *, rand() as random_number
> FROM image_img
> ORDER BY random_number DESC LIMIT 1
>
> I suspect that the rand() is not supported in MS Access. I am not sure
> what syntex to put in it's place...
>
> Can anybody tell me what I have done wrong?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
>
>

#3 (**permalink**) 11-Nov-2005, 20:09 PM

Wow that was quick!

Ok, I am a network guy, so bear with me.

I will try to drop that statement, and see what happens. I am using an
extension for Dreamweaver... I got the statement from a tutorial. I am
assuming that it is for a MySQL DB as they refer to a php file...

Regarding the limit 1 clause, I have no idea. I am just struggling through
this.

I am ultimately attempting to create an image gallery that allows users to
upload and display images...

Thanks in advance!

"Jeff Boyce" wrote in message
news:%23%230FSsl5FHA.2888@tk2msftngp13.phx.gbl...
> Marc
>
> If I were testing this SQL, I would first drop the ORDER BY clause and see
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
> what Access returns for the rest.
>
> What is the purpose of the "LIMIT 1" clause?
>
> Jeff Boyce
>
>
> "Marc Everlove" wrote in message
> news:%23oz7mkl5FHA.2364@TK2MSFTNGP12.phx.gbl...
>> Here is the statement...
>>
>> SELECT *, rand() as random_number
>> FROM image_img
>> ORDER BY random_number DESC LIMIT 1
>>
>> I suspect that the rand() is not supported in MS Access. I am not sure
>> what syntex to put in it's place...
>>
>> Can anybody tell me what I have done wrong?
>>
>>
>
>

#4 (**permalink**) 11-Nov-2005, 20:09 PM

ok I tried

SELECT *, rand() as random_number
FROM image_img

and I got:

Undefined function 'rand' in expression

Looks like another looooong night...

Thanks!!!

"Marc Everlove" wrote in message
news:OFRhXyl5FHA.1276@TK2MSFTNGP09.phx.gbl...
> Wow that was quick!
>
> Ok, I am a network guy, so bear with me.
>
> I will try to drop that statement, and see what happens. I am using an
> extension for Dreamweaver... I got the statement from a tutorial. I am
> assuming that it is for a MySQL DB as they refer to a php file...
>
> Regarding the limit 1 clause, I have no idea. I am just struggling through
> this.
>
> I am ultimately attempting to create an image gallery that allows users to
> upload and display images...
>
> Thanks in advance!
>
>
> "Jeff Boyce" wrote in message
> news:%23%230FSsl5FHA.2888@tk2msftngp13.phx.gbl...
>> Marc
>>
>> If I were testing this SQL, I would first drop the ORDER BY clause and
>> see what Access returns for the rest.
>>
>> What is the purpose of the "LIMIT 1" clause?
>>
>> Jeff Boyce
>>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
>>
>> "Marc Everlove" wrote in message
>> news:%23oz7mkl5FHA.2364@TK2MSFTNGP12.phx.gbl...
>>> Here is the statement...
>>>
>>> SELECT *, rand() as random_number
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
>>> FROM image_img
>>> ORDER BY random_number DESC LIMIT 1
>>>
>>> I suspect that the rand() is not supported in MS Access. I am not sure
>>> what syntex to put in it's place...
>>>
>>> Can anybody tell me what I have done wrong?
>>>
>>>
>>
>>
>
>

#5 (**permalink**) 11-Nov-2005, 20:09 PM

The random number function in VBA is Rnd().
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173

If Rnd() is used in an SQL String in Access, it should be OK. However, you
are only using JET (NOT Access) and Rnd() may NOT work for you.

--
HTH
Van T. Dinh
MVP (Access)

"Marc Everlove" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
news:#w3ye6l5FHA.2776@TK2MSFTNGP15.phx.gbl...
> ok I tried
>
> SELECT *, rand() as random_number
> FROM image_img
>
> and I got:
>
> Undefined function 'rand' in expression
>
> Looks like another looooong night...
>
> Thanks!!!
>

#6 (**permalink**) 12-Nov-2005, 22:39 PM

Ok I rewrote the statement so it is now:

SELECT *
FROM image_img
ORDER BY rnd(1(id_img) * 0 + 1)

It doesn't return an error but the image is always the same... It doesn't
randomize!

What's amiss with it???

Thanks in advance!

"Van T. Dinh" wrote in message
news:%23u5Cbto5FHA.744@TK2MSFTNGP10.phx.gbl...
> The random number function in VBA is Rnd().
>
> If Rnd() is used in an SQL String in Access, it should be OK. However,
> you
> are only using JET (NOT Access) and Rnd() may NOT work for you.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Marc Everlove" wrote in message
> news:#w3ye6l5FHA.2776@TK2MSFTNGP15.phx.gbl...
>> ok I tried
>>
>> SELECT *, rand() as random_number
>> FROM image_img
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
>>
>> and I got:
>>
>> Undefined function 'rand' in expression
>>
>> Looks like another looooong night...
>>
>> Thanks!!!
>>
>
>
>

#7 (**permalink**) 12-Nov-2005, 22:39 PM

"Marc Everlove" wrote in message
news:uSvvTDx5FHA.2196@TK2MSFTNGP10.phx.gbl
> Ok I rewrote the statement so it is now:
>
> SELECT *
> FROM image_img
> ORDER BY rnd(1(id_img) * 0 + 1)
>
> It doesn't return an error but the image is always the same... It
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
> doesn't randomize!
>
> What's amiss with it???

See this link:

http://www.mvps.org/access/queries/qry0011.htm
Queries: Create a query that returns Random records

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

#8 (**permalink**) 13-Nov-2005, 17:33 PM

Ok, I did my best to follow the directions but now it returns the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'randomizer' in expression.

/uptest/ugh02.asp, line 14

Of course line 14 is where I open the recordset...

I added the module to the database, but frankly, I am a noob at this so I am not certain I did it correctly. I have been attempting to look up information on how to accomplish this all, but have not been very successful. However, the randomizer query you linked to has come up a few times, so it is probably the best option. Now if I could just figure out how to make it work...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173

I really appreciate all the assistance!

"Dirk Goldgar" wrote in message news:eTDXXKx5FHA.3592@TK2MSFTNGP12.phx.gbl...
> "Marc Everlove" wrote in message
> news:uSvvTDx5FHA.2196@TK2MSFTNGP10.phx.gbl
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
>> Ok I rewrote the statement so it is now:
>>
>> SELECT *
>> FROM image_img
>> ORDER BY rnd(1(id_img) * 0 + 1)
>>
>> It doesn't return an error but the image is always the same... It
>> doesn't randomize!
>>
>> What's amiss with it???
>
> See this link:
>
> http://www.mvps.org/access/queries/qry0011.htm
> Queries: Create a query that returns Random records
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>

#9 (**permalink**) 13-Nov-2005, 17:33 PM

How are calling your query? Is it from outside of Access (VB, VB.Net, ASP,
etc.)? If so, I'm afraid you're out of luck: you can only use VBA
user-defined functions from inside Access.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Marc Everlove" wrote in message
news:eJ1$eN85FHA.1028@TK2MSFTNGP11.phx.gbl...
Ok, I did my best to follow the directions but now it returns the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'randomizer' in
expression.
/uptest/ugh02.asp, line 14
Of course line 14 is where I open the recordset...
I added the module to the database, but frankly, I am a noob at this so I am
not certain I did it correctly. I have been attempting to look up
information on how to accomplish this all, but have not been very
successful. However, the randomizer query you linked to has come up a few
times, so it is probably the best option. Now if I could just figure out how
to make it work...
I really appreciate all the assistance!

"Dirk Goldgar" wrote in message
news:eTDXXKx5FHA.3592@TK2MSFTNGP12.phx.gbl...
> "Marc Everlove" wrote in message
> news:uSvvTDx5FHA.2196@TK2MSFTNGP10.phx.gbl
>> Ok I rewrote the statement so it is now:
>>
>> SELECT *
>> FROM image_img
>> ORDER BY rnd(1(id_img) * 0 + 1)
>>
>> It doesn't return an error but the image is always the same... It
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
>> doesn't randomize!
>>
>> What's amiss with it???
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7173
>
> See this link:
>
> http://www.mvps.org/access/queries/qry0011.htm
> Queries: Create a query that returns Random records
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>