MS Query in Excel

[p9j123]

I need help on MS Query, I am not sure if this is the correct venue.

I need to extract certain data from MS Access to Excel using a parameter between Start Date and End Date, currently I am using the below connection code (if that is what it called) It is working fine if the dates are already defined as shown on the blue highlight My problem, I cannot modify it in such a way that Start Date will be based on the value of cell $C$2 and End Date will be from $C$3.

I really need your help.

[h=3][SIZE=2]Sub Macro4()[/SIZE]
[SIZE=2]'[/SIZE]
[SIZE=2]' Macro4 Macro[/SIZE]
[SIZE=2]'[/SIZE]
[SIZE=2]
[/SIZE]
[SIZE=2]'[/SIZE]
[SIZE=2]    With ActiveWorkbook.Connections("Query from MS Access Database").ODBCConnection[/SIZE]
[SIZE=2]        .BackgroundQuery = True[/SIZE]
[SIZE=2]        .CommandText = Array( _[/SIZE]
[SIZE=2]        "SELECT `2015`.ID, `2015`.FName, `2015`.LName, `2015`.BDay, `2015`.Count" & Chr(13) & "" & Chr(10) & "FROM `2015` `2015`" & Chr(13) & "" & Chr(10) & "WHERE (`2015`.BDay>=[COLOR=#0000cd][B]{t" _[/B][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000cd][B]        , "s '1982-10-01 00:00:00'}[/B][/COLOR] And `2015`.BDay<=[COLOR=#0000cd][B]{ts '1991-05-21 00:00:00'}[/B][/COLOR])")[/SIZE]
[SIZE=2]        .CommandType = xlCmdSql[/SIZE]
[SIZE=2]        .Connection = Array(Array( _[/SIZE]
[SIZE=2]        "ODBC;DSN=MS Access Database;DBQ=C:\Users\xxx\Desktop\Admin Automation\Sample Files\Working File\OFT v1.accdb;DefaultDir=C:\Users" _[/SIZE]
[SIZE=2]        ), Array( _[/SIZE]
[SIZE=2]        "\xxxx\Desktop\Admin Automation\Sample Files\Working File;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _[/SIZE]
[SIZE=2]        ))[/SIZE]
[SIZE=2]        .RefreshOnFileOpen = False[/SIZE]
[SIZE=2]        .SavePassword = False[/SIZE]
[SIZE=2]        .SourceConnectionFile = ""[/SIZE]
[SIZE=2]        .SourceDataFile = ""[/SIZE]
[SIZE=2]        .ServerCredentialsMethod = xlCredentialsMethodIntegrated[/SIZE]
[SIZE=2]        .AlwaysUseConnectionFile = False[/SIZE]
[SIZE=2]    End With[/SIZE]
[SIZE=2]    With ActiveWorkbook.Connections("Query from MS Access Database")[/SIZE]
[SIZE=2]        .Name = "Query from MS Access Database"[/SIZE]
[SIZE=2]        .Description = ""[/SIZE]
[SIZE=2]    End With[/SIZE]
[SIZE=2]    ActiveWorkbook.Connections("Query from MS Access Database").Refresh[/SIZE]
[SIZE=2]End Sub[/SIZE]
[/h]

 

[p9j123]

I just recorded the macro, I am willing to try other options as long as it can accomplish the desired output.

 

[John_w]

Try this change:

        .CommandText = Array( _
        "SELECT `2015`.ID, `2015`.FName, `2015`.LName, `2015`.BDay, `2015`.Count" & Chr(13) & "" & Chr(10) & "FROM `2015` `2015`" & Chr(13) & "" & Chr(10) & "WHERE (`2015`.BDay>={t" _
        , "s '" & Format(Worksheets("Sheet1").Range("C2").Value, "YYYY-MM-DD") & " 00:00:00'} And `2015`.BDay<={ts '" & Format(Worksheets("Sheet1").Range("C3").Value, "YYYY-MM-DD") & " 00:00:00'})")

Change "Sheet1" to the name of the C2/C3 sheet if necessary.

 

[p9j123]

Thank you John