• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 693
  • Last Modified:

Microsoft Query Parameters.....simple!

I am using Microsoft Query within Excel to source some reports feeding from an access table.

I wanted to put filters on the data and have setup a feed that picks the filter criteria from another sheet within the excel workbook which works fine.

My problem is:
What if I want to show all records in the query and apply no filter? Based on the current configuration if I leave the cells that determine the filters blank then the query returns nothing. I thought by using "*" that might pull everything but it doesn't seem to.

I'm sure it must be simple!!!

Any ideas?

0
JGWY
Asked:
JGWY
1 Solution
 
joebrezCommented:
Do you have field in the query that might make it a null query?
0
 
JGWYAuthor Commented:
No, unless the last row of the access table which is always blank for the next line of data is causing this?
0
 
walterecookCommented:
How about posting the SQL.  Maybe you have an errant space in there or something.

Walt
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JGWYAuthor Commented:
HEre ya go....

SELECT `emea1-3`.ORIG_COST_CTR, `emea1-3`.CHRG_COST_CTR, `emea1-3`.LVL1_GEO_NM, `emea1-3`.LVL2_GEO_NM, `emea1-3`.LVL3_GEO_NM, `emea1-3`.LVL2_LOB_NM, `emea1-3`.LVL3_LOB_NM, `emea1-3`.LVL4_LOB_NM, `emea1-3`.`TIER$_PROD_DS`, `emea1-3`.TIER0_PROD_DS, `emea1-3`.TIER1_PROD_DS, `emea1-3`.YEAR_NO, `emea1-3`.MO_NO, `emea1-3`.SumOfBILL_CHRG_VOL_CT, `emea1-3`.SumOfBILL_CHRG_AM, `emea1-3`.BILL_CHRG_TYPE_CD, `emea1-3`.ORIG_COST_CTR
FROM `C:\Documents and Settings\jgwynn\Desktop\TechSingleInvoice`.`emea1-3` `emea1-3`
WHERE (`emea1-3`.ORIG_COST_CTR=?)

If I source the parameter for "orig_cost_ctr" as say sheet2!b4, and leave the cell blank, nothing is returned, in this case I want everything to be returned.)
0
 
walterecookCommented:
Therein lies the problem.  It sees it as empty and is thus looking for data that = ""
You'll probably need to evaluate that cell first with an if. If it's empty use the query with no where clause.  If it isn't, use the current query.

Walt
0
 
JGWYAuthor Commented:
is there not a wildcard that can be inserted into the cell that represents all fields?

I'm thinking of using around 5 parameters and this could be really messy if i have to evaluate all first?
0
 
walterecookCommented:
There is and it is *
But you are saying make the condition = to the contents of the cell.  The cell is empty which is ""
So the query says "ok find me records that = ""

Walt
0
 
usarianCommented:
If I could make a suggestion,  How about triggering a small vba routine to perform the query if the cell contains, say the word "all"?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now