Inventory spreadsheet with table parameters so I can select either retail or wholesale and then a price threshold

[jeff_cp]

I'm working on a spreadsheet for our inventory. The inventory is pulled out of a SQL database called Inventory. The retail price field is called PriceRetail and the wholesale price field is called PriceWholesale. The table starts in cell A6.

I have a dropdown in cell B3 so I can select "Retail" or "Wholesale" and then a threshold in cell B4 so I can enter a maximum price.

What I want to do, for example is select Retail in cell B3 and enter 10.00 in cell B4 and then refresh the query and it show me all inventory where the Inventory.PriceRetail field is equal to or less than 10.00.

I have everything built but having issues with my query. This is what I currently have:
SELECT Inventory.Stock, Inventory.PriceRetail, Inventory.PriceWholesale
FROM SQLUser.Inventory Inventory
WHERE (Inventory.PriceRetail<=?)

 

[mse330]

I assume you have double quotes in your VBA code where you insert in SQL query so you can close with something like below

WHERE (Inventory.PriceRetail<=" & Range("B3").Value & ")"

 

[jeff_cp]

I have the Inventory.PriceRetail parameter set inside the table so that's ok. I just can't figure out how to do an if/then statement inside the query. I would think it would need to be something like this gives me an error:

IF B3="Retail" THEN
SELECT Inventory.Stock, Inventory.PriceRetail, Inventory.PriceWholesale
FROM SQLUser.Inventory Inventory
WHERE (Inventory.PriceRetail<=?)

ELSE IF B3="Wholesale" THEN
SELECT Inventory.Stock, Inventory.PriceRetail, Inventory.PriceWholesale
FROM SQLUser.Inventory Inventory
WHERE (Inventory.PriceWholesale<=?)

 

[mse330]

Can you please post your VBA code ? At least the section where you do the SQL query hiding any username/passwords