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