Global Search Parameter – Microsoft Query

15 pts.
Tags:
Excel 2003
Microsoft Query
Query Parameters
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TrueBlue76
    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.
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following