This sample db
demonstrates application of search criteria based upon selections in three
multi-select list boxes. For a given list box, the search criteria is
deemed True if the field value happens to match any of the selected
items. Overall criteria is determined by application of And operator
between the outcomes pertaining to different list boxes.
For sake of
illustration, three different data types are covered by the three list
boxes in this sample, namely date, text and number. In case of text
type, handling of embedded quotes if any, within the field
values, has also been demonstrated.
Three different styles
of displaying the contents of list boxes have been shown as follows:
(a) No cascading - The
full list is always under display.
(b) Simple cascading
(hierarchical) - LstDate controls the contents of LstType and
LstSupp, while LstType controls the contents of LstSupp.
(c) FreeStyle cascade -
Whichever list box happens to be updated, causes filtering of contents
of other two list boxes accordingly.
For user's convenience,
facility for selecting or de-selecting the whole list box has been
provided.
Three different styles
of applying the search criteria are demonstrated for each style of
cascading:
(a) Permanent saved
query with embedded criteria clause.
(b) SQL built at run
time.
(c) Filter string
applied at run time.
Selection results for
each list box are stored in corresponding unbound text boxes as
comma separated strings. For text type data, each element of such a
string is enclosed within outer single quotes, after having replaced
each embedded single quote if any, by double single quotes (As the
outer enclosing character is single quote, embedded double quotes if
any, don't require any special treatment). For date type data,
each element of such a string is enclosed within outer hashes.
This arrangement eliminates
the need for repeated cycling through the list boxes while
testing the field values. The value is simply checked against the
contents of corresponding unbound text box, using IN operator. In case
of permanently saved query with embedded criteria clause, EVal()
function is used for facilitating interpretation of
superstring having IN operator.