We help IT Professionals succeed at work.

ORDER BY

chestera
chestera asked
on
110 Views
Last Modified: 2017-04-11
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

CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

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"
CERTIFIED EXPERT
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
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions