Excel SQL query

[ca143508]

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.

[Bowmore]

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.

[ca143508]

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 = ""

[ChrisL]

Would any of my functions here http://www.autoitscript.com/forum/index.php?showtopic=51952 help

[ca143508]

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

[Juvigy]

Try some very simple where clause and say what happened. Like:

Select * from table where value=1