Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Newsgroups  |  Paradox Web Sites  |  Paradox Book List  |  FAQs From The Corel FAQ Newsgroup  



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.