Excel external data query to database - I need to set a start and end date

  • Hi, Sorry if this has been answered before or is a very easy question but I did search the site and cannot find anything that makes sense to me, I have also tried different things myself. basically I have read only access to a 3rd party database holds all of my data, I have created a query that returns to excel a table of all the data I need (200 columns and 85000 rows) this is 1 year data and will get bigger, I need to present/report on this data but not at this size, is there anyway I can make the query bring back a date range as chosen by the user, say when I click refresh an input box comes up or set 2 x cells in the sheet with start and end then use that to run the query. Hopefully this all makes sense.

  • In your command text under Connection Properties, you will need to add the parameters in your WHERE clause,


    (YourTable.DateColumn>=? And YourTable.DateColumn<=?)

    Then click Parameters and identify the cells that contain the Start and End dates.

    When you refresh, it should use your date range.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    MS Excel MVP 2010-2016

  • So can you edit your original query?

    If so, then you just need to add the criteria for dates, places square brackets around any word. This will automatically create a parameter. And when you refresh, it should ask you for inputs.

    Once your query is refreshed, you can send back to excel and set your parameters based on cell entries.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    MS Excel MVP 2010-2016

  • Hi, I am about to pull my hair out I have googled this and followed your instructions and still not working, I can access the advanced editor and when I type anything with square brackets it comes up syntax error I have tried loads of different things and nothing works, my external query/table is called GSJOBFIL and the column I want to put the date parameter on is RECORDEDDATE. I did find other articles on this but when I follow the steps certain things are greyed out. I can send more info if need be.

  • Hi, I have managed to get the following line to work as a trade off #"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each Date.IsInPreviousNDays([RECORDEDDATE], 90)) I am sure that this can be done better, let me know if you want me to copy the whole query into here.