Jump to content

ORDER BY CASE causing some issues.


johnnyg24

Recommended Posts

Hello, I've been working on this problem for two days with no luck in fixing it. I am trying to select records using the IN() function and then sort then in the same order using a CASE statment. Here is my set up:

Dim cnDim strSQLCommandSet cn = Server.Createobject("ADODB.Connection")Set rs = CreateObject("ADODB.Recordset")With cn.Provider = "Microsoft.Jet.OLEDB.4.0".ConnectionString = "Data Source=c:mypathmyfile.xls;" & _"Extended Properties=""Excel 8.0;HDR=Yes;"";".openEnd With  sql = "SELECT [ITEM-NUMBER] FROM [Sheet1$] WHERE [ITEM-NUMBER] IN('HOOP-2','GRAV-1','URIA-1') ORDER BY CASE WHEN [ITEM-NUMBER] = 'HOOP-2' THEN '1' WHEN [ITEM-NUMBER] ='GRAV-1' THEN '2' WHEN [ITEM-NUMBER] = 'URIA-1' THEN '3' ELSE [ITEM-NUMBER] END" rs.open sql, _ cn, 3, 1

Then I get this error:

 

error '80004005' on the line for rs.open

 

I know the database is good, if I remove the case statement it works. Unfortunately, I need the results in a specific order.

 

​Any help would be greatly appreciated. Thank you.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...