Hi - I am trying to use ADODB connection to query an excel spreadsheet and read some values. One of the queries that I am trying to run is date format. One of the columns in the excel file has date in this format 'MM/DD/YYYY'
This is what I am playing with
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objConn
objRS.Source = "Select * from TEST123 where [End Date] = '1/31/2007'"
While Not objRS.EOF
But I get "[Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression.
There is no leading 0 before 1 in the file. I also tried
select * .... where [End Date] = 'datetime('1/31/2007','M/DD/YYYY')"
but that also errors out. Any other ideas?
This [End Date] will not work. Either change the column name to EndDate and drop the brackets from the query or replace the brackets with double or single quotes (I believe) - OR - make [End Date] a var and change your query to look like this:
EndDate = "End Date"
"Select * from TEST123 where " & EndDate" & " = '1/31/2007'"
I renamed the column to EndDate
When I try this
objRS.Source = "Select * from TEST123 where EndDate = '1/31/2007'"
I get the error i posted in my prior post
If I take out the single quotes around the date then I don't get any error but it doesn't go in to the while loop to print any value
Nope that doesn't work either.
I also tried below
CurDate = "6/30/2007"
CurDate = FormatDateTime(CurDate)
objRS.Open "Select * from TEST123 where EndDate = " &CurDate
it doesn't give any error, but the query doesn't return anything either.
I made sure the format of the excel cell is date where 1/31/2007 exist.
'Deal with the optional parameters
If vSheet = "" Then
vSheet = 1
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sFileName & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;"";"
objRecordset.Open "Select * FROM [" & vSheet & "$] where Date1=#"&KeyValue&"#", objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
DataRequired= objRecordset.fields("Name") ' you need to create an array if your objRecordset returns more than one record.