Change date to range

[BradleyS]

I have the following code created in an Excel macro and I would like to change the date range to a rolling date i.e. from today in the last year.

WHERE (slotapp.slotdate>='20001006' And slotapp.slotdate<='20131006')

Therefore 20131006 needs to reference today() and 20001006 needs to be 1 year back from today.

Could somebody help me create this in the code?
Thank you

    Range("A1").Select
    Application.CommandBars("Task Pane").Visible = True
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=P:\WizAppt;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes" _
        ), Array(";")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT slotapp.slotdate, slotapp.staff_id, slotapp.type, slotapp.session_id, slotapp.count, slotapp.time, slotapp.pat_id, slotapp.pat_label, slotapp.arrived, slotapp.in, slotapp.out, slotapp.expdur, s" _
        , _
        "lotapp.actdur, slotapp.comment, slotapp.date_made, slotapp.time_made, slotapp.who_made, slotapp.method" & Chr(13) & "" & Chr(10) & "FROM slotapp slotapp" & Chr(13) & "" & Chr(10) & "WHERE (slotapp.slotdate>='20001006' And slotapp.slotdate<='20131006') AND " _
        , "(slotapp.count=$1)" & Chr(13) & "" & Chr(10) & "ORDER BY slotapp.slotdate")
        .Name = "Query from Visual FoxPro Tables"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

 

[BradleyS]

OK

I have now decided to use a start and end date from 2 cells on a spreadsheet.
However, it isn't working in the code. If I use a msgbox it shows me the varibles sDate and fDate are correct, so could somebody tell me why it's not working in the code?

WHERE (slotapp.slotdate>='sDate' And slotapp.slotdate<='fdate')

 'Get the date range from the spreadsheet
    Dim sDate As String
    Sheets("Date_Range").Select
        sDate = Application.WorksheetFunction.Text(Cells(7, 2), "yyyymmdd")
    
    Dim fDate As String
        fDate = Application.WorksheetFunction.Text(Cells(7, 3), "yyyymmdd")
    
    Sheets("Data").Select
    Range("A1").Select
    Application.CommandBars("Task Pane").Visible = True
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=P:\WizAppt;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes" _
        ), Array(";")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT slotapp.slotdate, slotapp.staff_id, slotapp.type, slotapp.session_id, slotapp.count, slotapp.time, slotapp.pat_id, slotapp.pat_label, slotapp.arrived, slotapp.in, slotapp.out, slotapp.expdur, s" _
        , _
        "lotapp.actdur, slotapp.comment, slotapp.date_made, slotapp.time_made, slotapp.who_made, slotapp.method" & Chr(13) & "" & Chr(10) & "FROM slotapp slotapp" & Chr(13) & "" & Chr(10) & "WHERE (slotapp.slotdate>='sDate' And slotapp.slotdate<='fdate') AND " _
        , "(slotapp.count=$1)" & Chr(13) & "" & Chr(10) & "ORDER BY slotapp.slotdate")
        .Name = "Query from Visual FoxPro Tables"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With