Using ADO need to fetch data, for given date

[trprasad78]

Hi all

I got following code from google, i try to change as per my requirement , But i am getting date type mismatch.

User will give from date and to date it has to check "ENDDATE" match data has to copied

Original

Sub test2()

Dim sSQLQry As String
Dim ReturnArray
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String
'DBPath = ThisWorkbook.FullName 'Refering the sameworkbook as Data Source
'You can provide the full path of your external file as shown below
DBPath = "C:\Users\Remyaprasad\Desktop\EmployeeTimeSheet.xlsx"
sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
'If any issue with MSDASQL Provider, Try the Microsoft.Jet.OLEDB:
'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"


Conn.Open sconnect
sSQLSting = "SELECT * From [Sheet1$] " ' Your SQL Statement (Table Name= Sheet Name=[Sheet1$])
mrs.Open sSQLSting, Conn


'=>Load the Data into an array
'ReturnArray = mrs.GetRows
''OR''
'=>Paste the data into a sheet
Sheet1.Range("A2").CopyFromRecordset mrs
'Close Recordset
mrs.Close
'Close Connection
Conn.Close
End Sub

try to change

Sub test2()

Dim sSQLQry As String
Dim ReturnArray
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String
'DBPath = ThisWorkbook.FullName 'Refering the sameworkbook as Data Source
'You can provide the full path of your external file as shown below
DBPath = "C:\Users\Remyaprasad\Desktop\EmployeeTimeSheet.xlsx"
sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
'If any issue with MSDASQL Provider, Try the Microsoft.Jet.OLEDB:
'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

'x = InputBox("Please enter Starting Date", "Please Enter")
'y = InputBox("Please enter Ending Date", "Please Enter")
Conn.Open sconnect
sSQLSting = "SELECT * From [Sheet1$] WHERE endDate >= '01-05-2017' and endDate < '28-05-2017' " ' Your SQL Statement (Table Name= Sheet Name=[Sheet1$])
mrs.Open sSQLSting, Conn


'=>Load the Data into an array
'ReturnArray = mrs.GetRows
''OR''
'=>Paste the data into a sheet
Sheet1.Range("A2").CopyFromRecordset mrs
'Close Recordset
mrs.Close
'Close Connection
Conn.Close
End Sub

 

[dan_l]

Use "#" to work with dates. Also, use "/" in between elements of a date instead of the "-". So like:
sSQLSting = "SELECT * From [Sheet1$] WHERE endDate >= #01/05/2017# and endDate < #28/05/2017#"

Lemme answer your next question too:
you'll get input using a variable like

dim userLowerDate as date
dim userUpperDate as date

Your SQL will have to be chopped up to work with that:

ssql = "SELECT * FROM [sheet1$] WHERE enddate >= #" & userlowerdate & "# AND enddate < #" & userupperdate & "#;"

 

[trprasad78]

Use "#" to work with dates. Also, use "/" in between elements of a date instead of the "-". So like:
sSQLSting = "SELECT * From [Sheet1$] WHERE endDate >= #01/05/2017# and endDate < #28/05/2017#"

Lemme answer your next question too:
you'll get input using a variable like

dim userLowerDate as date
dim userUpperDate as date

Your SQL will have to be chopped up to work with that:

ssql = "SELECT * FROM [sheet1$] WHERE enddate >= #" & userlowerdate & "# AND enddate < #" & userupperdate & "#;"

Thank you so much, let me try and get back to you

Thank you