![]() |
![]() |
|
![]() |
Subject: FAQ:PdoxWin:Query-by-Form (Parameter Queries):2000.10.01 Version 1.0 (1999.09.20) edited by Paradox FAQ Team Version 1.1 (2000.10.01) edited by Mike Irwin Reposted by Mike Irwin: 2001.02.05 Reposted by Mike Irwin: 2001.02.10 Reposted by Mike Irwin: 2001.05.25 ==================== 0. Introduction ==================== This FAQ addresses the frequent request for a Paradox equivalent for the Access Parameter Query. For those unfamiliar with Access, a Parameter Query is one that, when run, prompts the user for some parameters with which to run an SQL query. This FAQ presents an alternative that you can create test, and become confident with before using as a template to adapt to suit the particular situations you encounter. ------------------------------- 0.1 Legal Info and Disclaimers ------------------------------- Paradox is a trademark of Corel. Borland Database Engine (BDE) is a trademark of Inprise. The information provided in this FAQ is provided "as is" and is not warranted in any way. The information provided in this FAQ is not endorsed or authorized by Corel or Inprise in any shape, form, or manner. The editors claim NO responsibility for ANY illegal activity regarding this file, or as a result of someone reading this file. You may distribute this file, as long as the copies are complete, unaltered, and are in electronic form only. ------------- 0.2 Feedback ------------- Please send feedback in a Corel Paradox newsgroup or the news:comp.databases.Paradox newsgroup to any of the FAQ Team mentioned in the "FAQ: FAQ FAQ" document. Please preface the subject of your post with the string "PDXWIN FAQ" to alert Team members to the function of the message. Please specify the FAQ name and section number the comment applies to, if any. ------------- 0.3 Attachment ------------- Attached to this FAQ is the file QueryByForm.Exe. This is a self-extracting Zip file created with WinZip 6. Running it will bring up a "Save As" dialog where d:\temp is offered as the default location. Change this to your Paradox :work: directory and let it extract the three files: Orders.db Orders.px QueryByForm.fsl All aliases in this example have been changed to ":work:" to ensure that it works as an example, and extra error trapping added to show something of how you should check to see that what your user provides is really what you can handle. ====================== 1. General Information ====================== In Access it is possible to create something called a Parameter Query. It is also possible to create such a thing in Paradox, but with a little more effort, because of the fact that this is an artifact incorporated into Access, and in Paradox one has to program it. ------------------------------- 1.1 What is a parameter query and when would you use one? ------------------------------- A parameter query is a query that when run displays its own dialog box prompting you for information, such as criteria for retrieving records or a value you want to insert in a field. You can design the query to prompt you for more than one piece of information; for example, you can design it to prompt you for two dates. Microsoft Access can then retrieve all records that fall between those two dates. Parameter queries are also handy when used as the basis for forms and reports. For example, you can create a monthly earnings report based on a parameter query. When you print the report, Microsoft Access displays a dialog box asking for the month that you want the report to cover. You enter a month and Microsoft Access prints the appropriate report. ------------------------------- 1.2 In Access ------------------------------- For information on creating a parameter query, please see the Access online Help System. You can also create a custom form or dialog box that prompts for a query's parameters instead of using the parameter query's dialog box. ------------------------------- 1.3 In Paradox ------------------------------- In Paradox there is no built-in Parameter Query, so you are required to go about the process slightly differently, and utilise the second Microsoft approach - that of creating a custome dialog box or form to prompt for a query's parameters. ========== 2. Example ========== ------------------------------- 2.1 The Query ------------------------------- In Paradox a query is stored by the QBE expert as a text file. Let us suppose that we have created a query against a table in :Data: called Orders, and that we are interested in seeing all the orders, the customer IDs, when the orders were made, and how much payment has been received so far. We could also look to see if payments were complete. Such a query might look something like the query below when saved to a .QBE file. QUERY ANSWER: :Private:OrderInfo1.DB :Data:Orders.DB | OrderID | CustomerID | Date | | Check | Check | Check | :Data:Orders.DB | TotalReceived | Complete | | Check | Check | ENDQUERY ------------------------------- 2.2 The Constraints & Requirements ------------------------------- To allow the user to set constraints on this query (just one user; within a date range, etc.), we will have to create a form, place appropriate fields on the form for input, and write a small amount of code behind a button for the users to press when they are ready for the query to run. Let us say that in this case we want to be able to enter an OrderID, a CustomerID, a data range (two fields), and a Completeness check. Any of these could be used alone, and any can be combined. ------------------------------- 2.3 Creating the Form and Query ------------------------------- 1. Create the basic query, as above, that gets everything, and save it to disk, as, for example, fred.qbe. The name is unimportant except for retrieving it later. 2. Create a form. Place on it 5 fields, label them appropriately, and give them names (use the Properties pop-up dialog). In this case we shall use fldOrderID, fldCustomerID, fldFromDate, fldToDate, and fldComplete. 3. Place a button on the form, labelling it "Run Query". Right click on the button, open its Object Explorer, and open the pushButton() method in the editor. ------------------------------- 2.4. The "Run Query" button ------------------------------- 4. Write the following code in the method: var stOrderID String stCustomerID String stFromDate String stToDate String stDateRange String stComplete String quQuery Query tvResults tableView endvar stOrderID = fldOrderID stCustomerID = fldCustomerID stFromDate = fldFromDate stToDate = fldToDate stComplete = fldComplete ;==============================OrderID parameter if stOrderID.isBlank() then stOrderID = "Check" else stOrderID = "Check, " + stOrderID endif ;.................CustomerID parameter if stCustomerID.isBlank() then stCustomerID = "Check" else stCustomerID = "Check, " + stCustomerID endif ;.................set up date range if stFromDate.isBlank() or stToDate.isBlank() then stDateRange = "Check" else stDateRange = "Check, " + ">= " + stFromDate + "<= " + stToDate endif ;................. if stComplete.isBlank() then stComplete = "Check" else stComplete = "Check, " + stComplete endif ;============================== quQuery = QUERY ANSWER: :Private:OrderInfo1.DB :Data:Orders.DB | OrderID | CustomerID | Date | | Check | Check | Check | :Data:Orders.DB | TotalReceived | Complete | | Check | Check | ENDQUERY errorTrapOnWarnings(Yes) quQuery.writeQBE(":Private:Query.QBE") try quQuery.executeQBE() tvResults.open(":Private:OrderInfo1.DB") tvResults.wait() tvResults.close() onFail msgStop("", "") endTry errorTrapOnWarnings(No) The whole of the script, in this case can be copied and modified with the exception of the query. From the word "QUERY" to "ENDQUERY" you should use the "Edit | Paste From ..." menu option to retrieve your basic general query ------------------------------- 2.2 Adding in your Query ------------------------------- 5. Finally, you need to alter the query so that it will respond to the data entered by the user. In this case we change it from quQuery = QUERY ANSWER: :Private:OrderInfo1.DB :Data:Orders.DB | OrderID | CustomerID | Date | | Check | Check | Check | :Data:Orders.DB | TotalReceived | Complete | | Check | Check | ENDQUERY to quQuery = QUERY ANSWER: :Private:OrderInfo1.DB :Data:Orders.DB | OrderID | CustomerID | OrderDate | | ~stOrderID | ~stCustomerID | ~stDateRange | :Data:Orders.DB | TotalReceived | Complete | | Check | ~stComplete | ENDQUERY The four "tilde variables" (~stOrderID, ~stCustomerID, ~stDateRange, and ~stComplete) originate, as you can see, from variables in the code that are filled with data from the fields on-screen. 6. Save the form and run it. Fill in some proper data and press the button, and you will be presented with a tableView of the results of the query.querybyform.exe Paradox Community Newsgroups |
![]() Feedback | Paradox Day | Who Uses Paradox | I Use Paradox | Downloads ![]() |
|
![]() The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation. Paradox is a registered trademark of Corel Corporation. ![]() |
|
![]() Modified: 15 May 2003 Terms of Use / Legal Disclaimer ![]() |
![]() Copyright © 2001- 2003 Paradox Community. All rights reserved. Company and product names are trademarks or registered trademarks of their respective companies. Authors hold the copyrights to their own works. Please contact the author of any article for details. ![]() |
![]() |
|