Avatar of chestera
chestera
 asked on

ORDER BY

Hi EE

I have the following and a mental block
Set rsBooked = "SELECT ID FROM tblBooked" & "ORDER BY [ID] ASC"

Not working where have I gone wrong. Any help appreiated

chestera
Microsoft Access

Avatar of undefined
Last Comment
chestera

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rgonzo1971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ryan Chong

reason being there is no space for your joined string and that created an syntax error

>> Set rsBooked = "SELECT ID FROM tblBooked" & "ORDER BY [ID] ASC"
Gustav Brock

Why not simply:

    Set rsBooked = "SELECT ID FROM tblBooked ORDER BY [ID] ASC"

or:

    Set rsBooked = "SELECT ID FROM tblBooked ORDER BY 1"

/gustav
Dale Fye

everyone is focusing on the SQL, except Rgonzo.

1.  you have to define the SQL correctly, Rgonzo did this  although I cannot understand at all why anybody feels the need to define the SQL string by separating the SELECT and Order By clauses:

strSQL = "SELECT ID FROM tblBooked ORDER BY ID"

2.  The second is that cannot open a recordset by setting a value to a string.

set rsBooked = currentdb.OpenRecordset(strSQL, , dbfailonerror)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
chestera

ASKER
Gustav Brock

Getting same error as with my example Compile error Type mismatch. Tried Ryans version same result

Alan
chestera

ASKER
Dale Fye

You are correct just tried Rgonzo 1971 and it worked. Thank you for your comment

Alan
chestera

ASKER
Thank your all for your help
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.