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
chesteraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try
Set rsBooked = CurrentDb.OpenRecordset("Select ID FROM tblBooked" & " ORDER BY [ID] ASC")
 

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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"
0
Gustav BrockCIOCommented:
Why not simply:

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

or:

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

/gustav
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Dale FyeOwner, Developing Solutions LLCCommented:
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)
1
chesteraAuthor Commented:
Gustav Brock

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

Alan
0
chesteraAuthor Commented:
Dale Fye

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

Alan
0
chesteraAuthor Commented:
Thank your all for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.