1

I am utilizing Microsoft Query in Excel to tap into an ERP table structure like Crystal would do.

In writing the SQL, is there a way to have a filter pulled from the active Excel worksheet that is embedded in the SQL instead of prompting and editing the query?

My main problem is a Like [Prompt]% in the Excel GUI for the users to change like order numbers.

Is it possible to do an off page reference from MS Query to Excel?

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
Corey B
  • 11
  • 1

1 Answers1

0

If by "Microsoft Query", you're talking about the window that looks like it was coded for Windows 95, stop using it. This is provided for retro-compatibility.

Anyway, if you've displayed the criteria bar in MS query, you can type a name between brackets e.g. [Something] and MS query will prompt you to fill a value.

Not what you want yet but getting close. When you return to Excel and refresh the query, the prompt will now offer you the possibility to use a cell instead of a value you need to type every type.

In the more modern connection utility accessible via menu data > Connections (+ available even if you created your table via MS Query btw), you can achieve that by using question marks in the WHERE clause.

For instance, instead of SomeField = 'SomeValue', write SomeField = ? Then, click on the Parameters button and you'll see all the parameters you've set, each of them can be attached to a cell's value.

FXD
  • 1,960
  • 1
  • 6
  • 9
  • Noted on the question mark. Any chance you have a recommendation for a platform that should be used? And is ?% valid - learning as I go. – Corey B Mar 17 '19 at 02:09
  • Oops! I understood neither of your questions ... what do you mean by platform? and ?% would be used to do what? – FXD Mar 17 '19 at 09:33
  • That is OK. You pointed me in the right direction. I will edit the where clause of the connection. – Corey B Mar 17 '19 at 16:35