Programming Tool Box/SIMS_2.iso/access/diverses/qbf/qbf.txt

Text File | 1995-01-30 | 7KB | 153 lines

1/30/95


For more complete information on the use of this Query-by-Form code see the
article in the February/March issue of "Access/Visual Basic Advisor" titled
"QBF, Generic Blackboxes and Gold-Plated Trophies".

To get a feel for what the QBF is capable of doing, open the enclosed MDB and
open the form called fdlgOrderInformation.  The sample tables comes from the
Orders.mdb that shipped with Access 2.0; there are 1,082 joined records in the
result set.  Enter sample criteria to any of the fields.  You may use a list
separator character (comma, by default) to specify as many disparate items as
you wish in a particular field.  Or you may enter a range of values, such as ab
- ca or $1,000 - $1,500.  At this time, ranges and lists cannot be used within
the same field.  When the query is built, default operators will be used if one
is not explicitly stated.  Character data operators default to "Like"; numeric
and dates to "=".  With dates there are several directives that can be used as
the criteria:  today, yesterday, tomorrow, month, mth, year, yr, fiscal year,
FY, FYTD; previous, last or next can precede many of these special directives;
abbreviations are supported in some cases.  The logical operator "Not" is
supported, and most of the special SQL operators will be passed through if used
([!a-c] is valid, for example, as is Not [a - t]).  Synonyms are valid in many
cases; "!" is valid in lieu of "Not".  White space is usually ignored.  All in
all the model has been designed to be very forgiving of the user; I have tried
to develop a syntax that would be reasonably straightforward to use with a
minimal amount of training.  Here are some examples of the type of entries you
can make to the QBF (this is by no means a complete list):


If the user enters...        This SQL expression is produced...
=====================        ==================================

Acme                         fieldname Like "Acme*"

Not Like Acme*               fieldname Not Like "Acme*"

Acme - Lucky                 fieldname >= "Acme" AND fieldname <= "Luckyzz"

Acme, Lucky, Safe            fieldname Like "Acme*" OR fieldname
                             Like "Lucky*" OR fieldname Like "Safe*"

= Acme Inc.                  fieldname = "Acme Inc."

>= Acme                      fieldname >= "Acme"

Null                         fieldname IS NULL

1,025.50 - 2,500             fieldname >= 1025.50  AND fieldname <= 2500

$13, 25.50, 1000.50          fieldname = 13 OR fieldname = 25.50 OR
                             fieldname = 1000.50

>= 10,000                    fieldname >= 10000

-10,000 : -5,000             fieldname >= -10000 AND fieldname <= -5000
                             Assumes range-specifier character changed to
                             a colon.

10/20/94                     fieldname = #10/20/94#

10/20/94 - 01/10/95          fieldname >= #10/20/94# and fieldname
                             <= #01/10/95#

10/93                        fieldname >=  #10/1/93# AND
                             fieldname <= #10/31/93#

< 10/25/92                   fieldname <= #10/25/92#

91                           fieldname  >= #01/01/91# AND
                             fieldname <= #12/31/91#

03                           fieldname >= #03/01/94# AND
                             fieldname <= #03/31/94#   Assumes month
                             entered and uses current year.

Today                        fieldname = #today's date#

...and all the other special date directives mentioned above.

If you have questions as to whether an alternative syntax might be supported,
either try out your suspicion or browse through the code (the former is *much*
easier than the latter).

Here is what you need to do to put this routine to work.  It is possible to set
up a QBF in under ten minutes.  The easiest way to get started is to open the
included dialogue form called fdlgOrderInformation and do a Save As to a file
name of your choice, and then proceed to make changes to your controls and
labels as described below.  This way you will automatically be able to take
advantage of all of the extended functionality of this QBF model.

1. Create a form of the dialogue type (unless you are using my sample as
a starting point). Dialogue forms stop all other processing within the
application, and are useful for tasks that must be accomplished before
some other task is initiated. Obviously in this case, the user must enter
criteria before the main form or report is executed.

2. Place any number of unbound controls on your form for accepting
user criteria; label each of these fields. A control is unbound if the
controlsource property is left empty.

3. Place the name of a previously saved query in the tag property of your
QBF form. This querydef will provide the basis for the filter string that
the QBF builds. The name should be inserted as follows, substituting
your actual querydef name: RecordSource: "your querydef name" .
The colon or other connecting characters are immaterial; the quotes
are necessary. One caveat: In order for the QBF to successfully
modify your querydef SQL statement, the where clause of your
querydef should be altered to read WHERE FALSE.

4. For each control that you would like to be considered in the query,
open the property sheet for the unbound control and place the
following directive in the control's tag property substituting your table
and field name: QueryField: "tablename.fieldname". Double quotes
must be used around the value. This information is stored in the tag so
that the generic QBF routine can later determine the data field against
which the user-entered criteria should be compared.

5. If you would like your QBF to interactively build the Where clause so that
the user can view the SQL as he/she tabs from field to field, then you must
include the following in each criteria field's After Update event property:
=MakeQuery(screen.activeform).

6. Create a push-button (or a control of your choice) which will have
attached to its click event a procedure that calls the following function
with a single parameter indicating the current form object:
=MakeQuery(screen.activeform).

7. Lastly, you should include on your form a text box called
txtNewSQL, and make the control not visible. This is where the
generic QBF will dump the newly created SQL string. You can take
this newly created SQL string and do any number of things with it.
Most likely you will want to pass it to the record source of your
primary form or to the record source of a report.

The routine has been designed to deal with the absence of many of the above
mentioned components.  For example, if the preview screen forms or the Show SQL
form do not exist, this routine will continue to work.

The QBF does much, much more than I have indicated in this brief note. A great
deal of this is discussed in the full article. The remainder still needs to be
documented. For now, besides the article, there's the code, and lots of it.

I am beginning work on optimizing this model for the client-server environment,
where selective querying obviously has its very strong merits.

Please feel free to comment. There's lots of room for improvement.


Craig Lyall
DiMento/Lyall, Inc.
Oakland, CA
70731,2704