ca143508 Posted May 7, 2009 Share Posted May 7, 2009 Hi Guys, I hope you can get me going here. I can get the basic SELECT * FROM query to populate a listview. I want to take it a step furthur and have it run something similar to the following.... $objRecordSet.Open ('Select [Field1], [Field2], [Field3], [Field4], [Field5], [Field6] FROM ' & $s_Tablename & ' WHERE ((([Field5])>="2009/05/07") AND (([Field6])<="2009/05/22"))', $objConnection, 3, 3, 0x0001) I get the following error: ==> The requested action with this object has failed.: and this indicates something wrong at the end of the string. Any advice would be greatly appreciated. Cheers Mike. Link to comment Share on other sites More sharing options...
Bowmore Posted May 7, 2009 Share Posted May 7, 2009 How are the 2 date fields formatted in Excel? If they are formatted as Dates rather than stings you may need to convert the values to strings or the string values "2009/05/07" to dates before doing the comparisons. "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook Link to comment Share on other sites More sharing options...
ca143508 Posted May 8, 2009 Author Share Posted May 8, 2009 At the moment both columns are formatted as straight text. You can see a copy of the function below... Basically when they change the $Date1 control it will call this function and replace the necessary date values. For this post, I have just placed some static dates. Const $adOpenStatic = 3 Const $adLockOptimistic = 3 Const $adCmdText = 0x0001 Global $s_Filename = FileGetShortName("File.xls") Global $s_Tablename = "[Sheet1$]" ; Source XLS data $objConnection = ObjCreate("ADODB.Connection") $objRecordSet = ObjCreate("ADODB.Recordset") $objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $s_Filename & "; Extended Properties=""Excel 8.0;HDR=Yes;"";") $objRecordSet.Open ('Select [Field1], [Field2], [Field3], [Field4], [Field5], [Field6] FROM ' & $s_Tablename & ' WHERE ((([Field5])>="2009/05/07") AND (([Field6])<="2009/05/22"))', $objConnection, 3, 3, 0x0001) $TotalDuration = 0 Do $Duration = StringTrimLeft(_DateAdd( 's', $objRecordSet.Fields(2).value, '1970/01/01 00:00:00'), 10) $TotalDuration = $TotalDuration + $objRecordSet.Fields(2).value GUICtrlCreateListViewItem ($objRecordSet.Fields(0).value & '|' & $Duration & '|' & $objRecordSet.Fields(2).value & '|' & $objRecordSet.Fields(3).value & '|' & $objRecordSet.Fields(4).value & '|' & $objRecordSet.Fields(5).value, $ListView1); $objRecordSet.MoveNext() Until $objRecordSet.EOF() $objConnection.Close $objConnection = "" $objRecordSet = "" Link to comment Share on other sites More sharing options...
ChrisL Posted May 8, 2009 Share Posted May 8, 2009 Would any of my functions here http://www.autoitscript.com/forum/index.php?showtopic=51952 help [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire Link to comment Share on other sites More sharing options...
ca143508 Posted May 10, 2009 Author Share Posted May 10, 2009 Guys, After having a bit more of a play today, it seems as though it is not just limited to the date fields. It whenever I stipulate a WHERE clause. Regardless of the column I query it goes down. Any other ideas??? Cheers Link to comment Share on other sites More sharing options...
Juvigy Posted May 11, 2009 Share Posted May 11, 2009 Try some very simple where clause and say what happened. Like: Select * from table where value=1 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