Multiple Values in a single field with Microsoft Query

  • Thread starter Thread starter Joey_83
  • Start date Start date
J

Joey_83

Hi Everyone,
Is there a way to have multiple values in one cell that will pass into
a Microsoft Query.
What I'm trying to do is enter multiple numbers (4567, 1234) into cell
B3 (they won't be sequential so can use >,< etc.) and get the query to
run.
I've posted the SQL below for my query. Just wondering if this was
possible or not without having to purchase add ins.

SELECT VDN_Data.Date, VDN_Data.dvdnVDN, VDN_Data.`VDN Name`,
VDN_Data.`Inbound Calls`, VDN_Data.`ACD Calls`, VDN_Data.`Aban Calls`
FROM `TeamStatsv1`.VDN_Data VDN_Data
WHERE (VDN_Data.Date Between B1 And B2) AND (VDN_Data.dvdnVDN=B3?).

I've tried to use all kinds of delimiters but none seem to work, and
I've looked everywhere but can't seem to find any answers....

Any help would be greatly appreciated!
 
In MS Query, build the query per normal.
View the Criteria window
Select the table field(s) that you want to supply cell parameter values for

For the criteria:
Enclose the parameter name in square brackets
Example: [prmCustNum]

When you run the query, you will be prompted to supply each parameter.

After the query runs properly, click the Return Data to Excel button.
On the popup window where you select the destination for the query data,
there is a [Parameters] button at the bottom.

Click the [Parameters] button
All parameters will be displayed
Select a parameter
Select "Get the value from the following cell"
Select the cell containing the parameter value
Repeat for each parameter

After completing the process, whenever you refresh the query, the values
from the parameter cells will be supplied to the SQL.

Thereafter, if you need to change the location of the parameter cells:
Right-click on the data table
Select: Parameters
That will open the Parameters window, where you will effect the changes.

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
Back
Top