Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

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

    e.g

    (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

    Comment


    • #3
      Thank you, I will give this a go and let you know how I get on.

      Comment


      • #4
        Hi my command text is greyed out and will not let me edit, sorry but I am quite a novice at this, I have tried different things to get round this but to no avail.

        Comment


        • #5
          Is the sheet protected?
          Where there is a will there are many ways. Finding one that works for you is the challenge!

          MS Excel MVP 2010-2016

          Comment


          • #6
            No the sheet is not protected, I am using excel 2016 and created the sheet from scratch so I should have full permissions.

            Comment


            • #7
              Are you able to edit the query by click Edit Query?

              here's a link to show how to set the date parameters in MSQuery: http://dailydoseofexcel.com/archives...-data-queries/
              Where there is a will there are many ways. Finding one that works for you is the challenge!

              MS Excel MVP 2010-2016

              Comment


              • #8
                Hi Thanks for this and sorry it has taken me so long to reply, I am using excel 2016 and the edit screen is totally different to the article, sorry but I am a complete novice with this.

                Comment


                • #9
                  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

                  Comment


                  • #10
                    Thank you I will give this a go and let you know how I get on.

                    Comment


                    • #11
                      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.

                      Comment


                      • #12
                        Can you post your actual query?
                        Where there is a will there are many ways. Finding one that works for you is the challenge!

                        MS Excel MVP 2010-2016

                        Comment


                        • #13
                          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.

                          Comment

                          Working...
                          X