Global Search Parameter – Microsoft Query
I have a microsoft query setup in excel 2003 using parameters where the user inputs a departure airport and then a destination airport to retrieve various sales data. However I want to be able to leave the departure parameter blank so that on occasions the user can retreive ALL departure aiports that have a destination of the required airport. I have tried using the IS NULL criteria but this only relates to the actuall fild in the database, there are no null field in the database.
Software/Hardware used:
Software/Hardware used:
ASKED:
August 5, 2009 12:02 PM
UPDATED:
August 10, 2009 7:08 PM
Answer Wiki:
It is possible, but how to do that will depend on the database you are getting the data from.
In general, you need to edit the query's SQL statement, and use a function that replaces NULL with a specified value. If the database is Oracle, for example, that function would be NVL, and if it is Sql Server, the function would be ISNULL.
The function should be applied to the parameter (not the database column).
For example:
If the query's SQL statement looks like this:
<pre>SELECT FIELD_X,FIELD_Y,FIELD_Z
FROM <OWNER>.SALES_TABLE
WHERE (DEPARTURE_AIRPORT_ID = ?
AND DESTINATION_AIRPORT_ID = ?)</pre>
If you are using SQL Server, the modified statement should look like this:
<pre>SELECT FIELD_X,FIELD_Y,FIELD_Z
FROM <OWNER>.SALES_TABLE
WHERE (DEPARTURE_AIRPORT_ID = <b>ISNULL(?,DEPARTURE_AIRPORT_ID)</b>
AND DESTINATION_AIRPORT_ID = ?)</pre>
This way, when the departure parameter is left NULL, the departure columns is compared to itself.
------------------------------------
Instead of “IS Null” can you set up “ALL” when the customer wants to leave the departure airport blank. Your query would then use “ALL” for departure.
Or may be set up another button for query when they want all reports from destination airport.
To see all answers submitted to the Answer Wiki: View Answer History.
Cheers for the replies, sorry it took a while to get back to you. I have tried adding the ISNULL section to the SQL query in Microsoft query but I keep getting an error stating – Parameters Are Not Allowed In Queries That Can’t Be Displayed Graphically.
My SQL statement looks like this at the moment.
SELECT SBSFlight.FlightNr, SBSFlight.LocalDepDate, SBSProductTransactionSummary.ProductID, SBSProduct.ProductName, SBSProductTransactionSummary.SoldQuantity, SBSProductTransactionSummary.CostPrice, SBSProductTransactionSummary.RetailPrice
FROM sbs32.dbo.SBSFlight SBSFlight, sbs32.dbo.SBSProduct SBSProduct, sbs32.dbo.SBSProductGroup SBSProductGroup, sbs32.dbo.SBSProductTransactionSummary SBSProductTransactionSummary
WHERE SBSFlight.FlightKey = SBSProductTransactionSummary.FlightKey AND SBSProduct.ProductID = SBSProductTransactionSummary.ProductID AND SBSProductGroup.ProductGroupID = SBSProduct.ProductGroupID AND ((SBSFlight.LocalDepDate>=? And SBSFlight.LocalDepDate<=?) AND (SBSFlight.DepAirportID=?) AND (SBSFlight.DestAirportID=?) AND (SBSProductGroup.ProdGroupName=?))
ORDER BY SBSFlight.LocalDepDate, SBSFlight.FlightNr
Basically the query just retrieves sold product based on the departure date from the query and the route specified in the query. At present I can only report on a valid airport id for both Departure and destination. I want to be able report on all flight departing from Manchester to ANY destination instead of having to select a destination from the DB. I have used the MS Query Wizard to setup the parameters in the first place. Any more help would be greatly appreciated.