ORDER BY

chestera
chestera used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

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

Open in new window

Regards
Ryan ChongSoftware Team Lead

Commented:
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"
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Why not simply:

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

or:

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

/gustav
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

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

Author

Commented:
Gustav Brock

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

Alan

Author

Commented:
Dale Fye

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

Alan

Author

Commented:
Thank your all for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial