Responses in-line.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<tr******@comca st.netreplied in message
news:2a******** *************** ***********@d1g 2000hsg.googleg roups.com...
>
Just to let you know, I LOVE your website. I just used your code for
building a dynamic WHERE string to produce a custom report generator.
It absolutely rocks and our Sr. developer is blown away. He has no
idea where I get my secret powers from.
Excellent. Great to know it's being put to good use. That particular example
is a technique I use over and over in different contexts.
BTW, I know you did a separate listbox tutorial for generating a WHERE
statement, but it would have been great if you would have put that as
an example on the main Search Criteria tutorial. I'm about to create
another report generator incorporating a listbox along with combo
boxes. It is going to be a little daunting but after the success I had
with this last project, I have high hopes.
Too many new ideas in one example can lead people to just give up, so I
think the examples are best separately. It's not hard to combine them
though: just declere 2 different string variables, so you can build up the
list box string first, and then concatenate it into the main filter string
and extra brackets and and AND on the end.
I also have a couple of questions on the search criteria:
I want to offer some alternatives in a combo box for use in strWhere-
things like 'Is Null', 'Is Not Null', or a combination of values. For
instance, we have three priority ratings- 1, 3 or 5. I already offer a
choice for each separate priority rating, but not for all of them
together- '1,3 and 5'. (There can be null values in this field, so it
does me little good to leave it empty.) I'm a little unsure of how to
do this.
Yes, I use an unbound combo box for the operator. User selects a value, and
you use it to build the filter string the the appropriate wildcards.
For a Text field, the combo has properties like this:
Column Count: 2
Column Widths: 0
Bound Column: 1
Row Source Type: Value List
Row Source: 0;"Is";1;"Is Not";4;"Begins" ;5;"Not
Begin";6;"Conta ins";7;"Not Contain";8;"End s";9;"Not End";10;"Blank" ;11;"Not
Blank"
The code then does a Select Case on the combo's value, and builds the string
using = for 0, <for 1, Like "xx*" for 2, ... and Is Not Null for 11.
Also, do you have any plans for making a search criteria using AND
and/or OR?
The problem here is the way to interface the bracketing, since:
a AND (b OR c)
is not the same as:
(a AND b) OR c
where a, b, and c are criteria expressions such as "SomeField Is Null")
As you found, the multi-select list box is one easy way to offer an OR
within a bunch of ANDs.
Thanks for all the great info you have on Access. You have no idea
how much your site has helped me out. I'm a big fan.
Great. All the best as you develop your Access understanding, and put it to
good use, Troy.
