johnnyg24 Posted September 16, 2014 Share Posted September 16, 2014 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 More sharing options...
justsomeguy Posted September 16, 2014 Share Posted September 16, 2014 Make the case a column in the result and then sort by that column. Link to comment Share on other sites More sharing options...
johnnyg24 Posted September 16, 2014 Author Share Posted September 16, 2014 Not sure what you mean by "make the case a column in the results". The ITEM-NUMBER is already a column in the database (excel file). I'm new to SQL so could you provide an example please? Link to comment Share on other sites More sharing options...
justsomeguy Posted September 16, 2014 Share Posted September 16, 2014 I don't write a lot of SQL for Excel, but it's probably something like this: SELECT [ITEM-NUMBER], CASE ... END AS [CASE-NUMBER] FROM ... ORDER BY [CASE-NUMBER] Link to comment Share on other sites More sharing options...
johnnyg24 Posted September 16, 2014 Author Share Posted September 16, 2014 Thanks, I did a little more digging and it may not be possible to use a case statement with Excel. Maybe I should look into sorting the Recordset set after the query?? Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now