How to derive data 'between dates' using SQL in VBA

[ThrottleWorks]

Hi,
I am new to SQL, I am trying to derive data based on the dates.
There are 3 conditions.
1) Date should be between 29/10/2015 and 27/10/2015, both 29 and 27 are considered
2) Name should be Yamaha
3) Region should be East
I am able to manage condition 2 and 3 however I am not able to do condition 1.
I tried copying various solutions online but I was not able understand it.
Sorry for asking readymade answers, please help if possible.
Will try to upload sample file in some time.

I am using below mentioned code.

Sub sbADOExample_4()
    'Column A Serial No
    'Column B Date
    'Column C Name
    'Column D Region
    'Column E Count
    'No of rows in table, 11
    'No of columns in table 5
   
    Dim Macrobook As Workbook
    Set Macrobook = ThisWorkbook
   
    Dim MasterSht As Worksheet
   
    Set MasterSht = Worksheets("Master")
   
    Dim TempRng As String
    TempRng = Replace(MasterSht.Range(Cells(1, 1), Cells(11, 5)).Address, "$", vbNullString)
   
    Dim MyQuery_1 As String
    Dim ReturnArray
   
    Dim NewConnection    As New ADODB.Connection
    Dim New_RecordSet    As New ADODB.Recordset
   
    Dim DBPath As String
    Dim sconnect As String
   
    DBPath = ThisWorkbook.FullName
   
    sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & _
    ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
   
    NewConnection.Open sconnect
   
   
    sSQLSting = "SELECT * FROM [" & MasterSht.Name & "$" & TempRng & "] WHERE Name = 'Yamaha' AND Region='East';"
   
    New_RecordSet.Open sSQLSting, NewConnection
    Sheet2.Range("A2").CopyFromRecordset New_RecordSet
   
    New_RecordSet.Close
    NewConnection.Close
End Sub

 

[Deepak]

Check it.

sSQLSting = "SELECT * FROM [" & MasterSht.Name & "$" & TempRng & "] WHERE Date BETWEEN #10/27/2015# AND #10/29/2015# AND Name = 'Yamaha' AND Region='East';"

 

[ThrottleWorks]

Hi @Deepak Sir, thanks a lot for the help. Now I understand how to derive data based on dates. Have a nice day ahead.

 

[Deepak]

Good to know that!!

date format should be in us format [mm/dd/yyyy] in sql string.

 

[ThrottleWorks]

Hi @ Deepak Sir, sure, I will keep this in mind while using SQL with VBA. Thanks a lot.

 

[Deepak]

Welcome buddy!!

 

[ThrottleWorks]

Hi,

I am getting bug with below line.

sSQLSting = "SELECT * FROM [" & TempSht.Name & "$" & SqlRng& "] WHERE RecCoverageArea = 'London'"

I checked TempSht.Name in Im Window, it's correct.
I checked SqlRngaddress in Im window, it's correct.

Even tried SqlRng.select to check if macro getting correct range.

'RecCoverageArea' is present in Column A.
'London' is present at A2.

But I am getting bug as Runtime error 13 Type mismatch.

can anyone please help me in this. What would be the cause. What should I check.

Dim MyQuery_1 As String
    Dim ReturnArray
   
    Dim NewConnection As New ADODB.Connection
    Dim New_RecordSet As New ADODB.Recordset
   
    Dim DBPath As String
    Dim sconnect As String
   
    DBPath = ThisWorkbook.FullName
   
    sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & _
    ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
   
    Dim SqlRng As Range
    Set SqlRng = TempSht.Range(TempSht.Cells(1, 1), TempSht.Cells(TempLr, 1))
   
    NewConnection.Open sconnect
    sSQLSting = "SELECT * FROM [" & TempSht.Name & "$" & SqlRng & "] WHERE RecCoverageArea = 'London'"
   
    New_RecordSet.Open sSQLSting, NewConnection
   
    TempLr = LdnSht.Cells(Rows.Count, 1).End(xlUp).Row + 1
    LdnSht.Cells(TempLr, 1).CopyFromRecordset New_RecordSet
   
    New_RecordSet.Close
    NewConnection.Close