User input (drop-down or fill in a cell) changes sql query parameters

[jschindler]

Hello,

My goal is for the user (who needs info on past shipments) to input the info they know to filter the data set and find all shipments with the parameter they select.

I currently have a query that pulls data into a table and the user filters on that table. I have a separate sheet for each location running all data for 2019, but the file is huge and my users won't always know which location to select to filter their data. So instead of them having to click through each sheet (30 sheets in all) and filter on each table, I'd like to include a drop-down menu (or several) on a landing sheet that the user can filter the data and the output would contain only the shipments they're searching for.

I need drop-down menus for the headers in my "select" statement. My preference is for the user to input as many of these headers as they know, and the query runs based on their input. So if they only know the "order_nbr" and/or "item_txt", it would output all data related to the shipments with those matching parameters.

My sql query is located in the "command text" section in connection properties within the excel file. How do I accomplish my goal?

Here's my current query:

select
shpd_dt, shpmt_nbr, ord_nbr as order_number, sellng_loc_cd as brwy, shp_to_cust_nbr as CUST, item_txt as PRODUCT, inv_ln_qty

from
JPE.SHPMT_COD_UP

where
ln_prod_grp_cd = 'PRODUCT'
and to_char(shpd_dt, 'YYYY') in ('2019')
and shpmt_nbr like '2%'
and sellng_loc_cd in ('TEXAS')
order by shpd_dt asc

[alex5421]

let me know if you get to know .. i have same query ..