April 27, 2014 at 3:41 am
Hello All,
I have excel spreadsheets that I use to connect to SQL Server by ODBC for my end users. I have tried a few different ways trying to figure out how to add a search feature on the spread sheet, so the end user can simply enter the badge no or passport number and this would then pull back records only for that person.
Things I have tried; using ? for a parameter in the script like
select * from table
where badgenumber = '?'
Thanks for any help.
April 27, 2014 at 4:59 am
...
Design the query using msquery (in excel tools-> data -> get external data then select the dsn/odbc link).
Any criteria fields in the msquery should have a value of []. The part between the square brackets can have prompt text like [enter username].
Run the query in msquery (using the exclaimation mark at top) and you will get a prompt to enter a value.
Return the data to excel (file menu).
Right click on the cells that now contain data and choose parameters. This dialogue will list the parameters available and you can then select the appropriate cells for the values to pass into the query.
Source: http://community.spiceworks.com/topic/174004-query-a-database-using-a-parameter-in-excel, Answer from Paul730
How did I find it? A program called Google...
April 28, 2014 at 1:48 am
LutzM,
Thanks for pointing me in the right direction. Sometimes just phasing the question in the correct way makes using google much easier to find the answers.
Thanks for posting, I was able to find the answer because of your post.
April 28, 2014 at 6:30 am
You can also have a cell referenced in the worksheet, and when changed, it will reresh the query.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply