Excel Parameters Help!

I have a database getting pulled into excel for ease of information without using the database program. The SQL Code is ready to go, we now just need parameters to narrow down a huge record of results. The first parameter is to get the Company Number, and we need a second parameter to get the invoice number. This is only one sheet of records we are pulling, so knowing this code will go a long way.

I want it so they can enter the company number, and the results shrink to just info for that number. Then, they can enter an invoice number right next to that parameter cell, and the results will then shrink to just that invoice number. I have yet to figure out a way to get two or more parameters to work tandem together. Below is the code I thought would work, but as of now, its only one or the other:


WHERE JCCM.Company Like ? AND JCCM.Invoice Like ?

Please help!

I don't think you're looking for a partial match on company number or invoice number, so the Like isn't necessary.

Please Login or Register  to view this content.

should suffice. Were you forgetting the quotes?

Originally Posted by cyiangou

I don't think you're looking for a partial match on company number or invoice number, so the Like isn't necessary.

Please Login or Register  to view this content.

should suffice. Were you forgetting the quotes?

That doesn't provide a cell parameter though. They aren't going to go into the SQL query every time to change the values.

Oh ok, I misunderstood. You're using datatables and data binding? Not my area really. I was talking vba-driven unbound data access.

Originally Posted by cyiangou

Oh ok, I misunderstood. You're using datatables and data binding? Not my area really. I was talking vba-driven unbound data access.

Yeah, a database through MS Query. I'm sorry, I should have mentioned that.

Originally Posted by alansidman

Check out the Query Manager Add in at this link.

http://www.jkp-ads.com/download.asp

Not able to get to that page due to our network policies.

Originally Posted by alansidman

Check out the Query Manager Add in at this link.

http://www.jkp-ads.com/download.asp

Do you have any other options?

How about this article?
http://www.tushar-mehta.com/excel/ne...d_query_4.html

Originally Posted by cyiangou

How about this article?
http://www.tushar-mehta.com/excel/ne...d_query_4.html

Only helps for single parameters. I need it so when they enter one parameter in a cell, records reduce to that parameter. Then, when they enter another parameter, the results trim even further down.

Would you entertain a vba method of setting parameters?

Originally Posted by cyiangou

Would you entertain a vba method of setting parameters?

Absolutely! I would like to try to figure out a solution instead of paying data services thousands of $$.

Originally Posted by cyiangou

Would you entertain a vba method of setting parameters?

Here is my current code:

HTML Code: 

I would like to have a parameter in one cell off to the side of the table for APTD.APCo and another cell with a parameter for APTH.APRef

No doubt the forum firewall won't let me post SQL directly so here's a bitmap of the code in the ThisWorkbook module:
msquery.jpg

You get the idea. I'm sure you'll easily substitute your SQL.

Here's the workbook also:
msquery_cy.xlsm

Although I think you'll be disappointed with your 'honing in' effect. You'll get either:

All records
or
All records matching Company Number
or
All records matching Invoice Number
or
One record (or set) matching both Company Number and Invoice Number

But I don't think you will see a smoothly reducing resultset.

Originally Posted by cyiangou

Although I think you'll be disappointed with your 'honing in' effect. You'll get either:

All records
or
All records matching Company Number
or
All records matching Invoice Number
or
One record (or set) matching both Company Number and Invoice Number

But I don't think you will see a smoothly reducing resultset.

Yikes. That looses me a little. Let me add I am not proficient in VBA or SQL lol.