none
Using a Form to Change Date Filter on MS Query in Excel VBA RRS feed

  • Question

  • Hello,

    I am having trouble changing the date filter on a Query that I set up in a VBA Macro. Each month I need to change the date range and did not want to change it in the code everytime because I am pulling multiple queries and having arrays named with VBA. This is the only part that I have not been able to automate.

    The dates in Bold below are where I am trying to use the dates entered into the form. Is this possible????

    Sub PostProgramQrys()

    Dim StartDate As Date
    Dim EndDate As Date

    frmPostProgramQrys.txtStartDate = StartDate
    frmPostProgramQrys.txtEndDate = EndDate

        Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "SalesSalesGroupState"
        Sheets("SalesSalesGroupState").Select
       
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "ODBC;DSN=Mend;UID=HMF04473;;SERVER=Mend;", Destination:=Range("$A$1")). _
            QueryTable
            .CommandText = Array( _
            "SELECT SALES_SALESEXPORTGROUPSTATE.SALEDATE, SALES_SALESEXPORTGROUPSTATE.MDLMAKE, SALES_SALESEXPORTGROUPSTATE.MDLYEAR, SALES_SALESEXPORTGROUPSTATE.MODELLEFT, SALES_SALESEXPORTGROUPSTATE.INV_MODEL_NUM," _
            , _
            " SALES_SALESEXPORTGROUPSTATE.MDLNAME, SALES_SALESEXPORTGROUPSTATE.SALECOUNT, SALES_SALESEXPORTGROUPSTATE.STATE" & Chr(13) & "" & Chr(10) & "FROM SAS_SALES.SALES_SALESEXPORTGROUPSTATE SALES_SALESEXPORTGROUPSTATE" & Chr(13) & "" & Chr(10) & "WHERE (SALES_SAL" _
            , _
            "ESEXPORTGROUPSTATE.SALEDATE>={ts '2012-08-01 00:00:00'} And SALES_SALESEXPORTGROUPSTATE.SALEDATE<={ts '2012-08-31 00:00:00'})" _
            )
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Table_Query_from_Mend"
            .Refresh BackgroundQuery:=False
        End With

    Wednesday, September 19, 2012 12:37 AM

Answers

  • Try replace SQLString that way:

      .CommandText = Array( _
            "SELECT g.SALEDATE, g.MDLMAKE, g.MDLYEAR, g.MODELLEFT, g.INV_MODEL_NUM," _
            , _
            " g.MDLNAME, g.SALECOUNT, g.STATE FROM SAS_SALES.SALES_SALESEXPORTGROUPSTATE g," _
            , _
            " WHERE (g.SALEDATE>={ts '" & frmPostProgramQrys.txtStartDate & "'}" _
            , _
            " And g.SALEDATE<={ts '" & frmPostProgramQrys.txtEndDate & "'})" _
            )


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by MDPopiolek85 Thursday, September 20, 2012 11:37 PM
    Wednesday, September 19, 2012 7:11 AM
    Answerer

All replies

  • Try replace SQLString that way:

      .CommandText = Array( _
            "SELECT g.SALEDATE, g.MDLMAKE, g.MDLYEAR, g.MODELLEFT, g.INV_MODEL_NUM," _
            , _
            " g.MDLNAME, g.SALECOUNT, g.STATE FROM SAS_SALES.SALES_SALESEXPORTGROUPSTATE g," _
            , _
            " WHERE (g.SALEDATE>={ts '" & frmPostProgramQrys.txtStartDate & "'}" _
            , _
            " And g.SALEDATE<={ts '" & frmPostProgramQrys.txtEndDate & "'})" _
            )


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by MDPopiolek85 Thursday, September 20, 2012 11:37 PM
    Wednesday, September 19, 2012 7:11 AM
    Answerer
  • Thanks!!!

    That worked. I tried doing that before except by inputing the variable name that I had created. There was a loss of formating during this transfer to a variable that kept changing whatever date I put in to some 1900's date.

    I guess I just need to keep it simple.

    Thursday, September 20, 2012 11:37 PM