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.
Excel external data query to database - I need to set a start and end date
-
-
-
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.
-
Thank you, I will give this a go and let you know how I get on.
-
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.
-
Is the sheet protected?
-
-
No the sheet is not protected, I am using excel 2016 and created the sheet from scratch so I should have full permissions.
-
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/2004/…l-data-queries/
-
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.
-
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.
-
Thank you I will give this a go and let you know how I get on.
-
-
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.
-
Can you post your actual query?
-
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.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!