How to use (and query for) a multiple selection option box?

[RYankowitz]

Help, please... I have hit a brick wall.

I have a need to allow a user to select any or all choices in a group while entering data, such as:

[ ] Food [ ] Water [ ] Air

Elsewhere the user will choose one of the items from that group, which will be used to drive a query to select records containing that (and only that) choice.

How I've done it so far - the way that doesn't work
Each of the choices is contained in an unbound check box, with the first box's value=1, the second=2, the third=4. The sum of the values is stored in the database table's field (call it Essentials).

The idea is to use the logical OR and AND operators to mask the correct bits in Essentials in order to filter out the unwanted records.

The problems?
1. I cannot use AND or OR operators in the criteria row of a Select query, since the query assumes I want to filter this OR that, rather than do a bitwise OR of this/that.
2. I tried using the EVAL() function, which should evaluate the expression first (and does, with most arithmetic functions), but using AND or OR inside it does not work.
3. I am successful putting the logic in a custom Function procedure, and calling that function from the criteria line of the query, but this method is amazingly slow.

So, I repeat: Help, please... I have hit a brick wall.

Perhaps there's another way around the problem you know about?

 

[Steve101]

RYankowitz,

You should be able to achieve what you want by writing a couple of "wrapper" functions in a module, then calling these functions from the query. They're called "wrapper" functions, as they insulate the logical bitwise AND, OR type operators from the query syntax.

For example, define the following two functions in a global module called modUtils:

Function AandB(a, b)
AandB = a And b
End Function

Function AorB(a, b)
AorB = a Or b
End Function

Assuming we have a table called Table1, with numeric fields a and b, then the query:

SELECT a, b,
AandB(a,b) AS AandB,
AorB(a,b) AS AorB
FROM Table1;

would return the following, for values of a and b in the table as shown in the first two columns:

a b AandB AorB
1 4 0 5
2 4 0 6
3 4 0 7
4 4 4 4
5 4 4 5
6 4 4 6

I have not shown it here, but you could equally well use the functions as part of the Criteria within the query.

Hope that this helps,
Cheers,
Steve

 

[rajeessh]

Try putting them as radio inseide a option Frame and use
them in \the code
as Me.OptFrame.value
if the checkboxes are going to be mutually exclusive


Or if all checkboxes are checked

Form the crietria in AfterUpdate and append it to your
query

 

[RYankowitz]

Steve,

Thanks for the response. What you've described I had already done (see problem #3 in my original message), and it works, but at a frightful cost in speed. A query with 3,300 records that takes about 1/2 second without the function takes over 5 seconds with it. Because of the particular requirements of my application this performance hit is way too high.

I appreciate your effort, especially since it was formatted so neatly (which fits my philosophy: It doesn't matter if it works good as long as it looks good!). I'm just buttering you up so you'll consider continuing the search to help me.
_________________________________________________________

Rajeessh,

Thanks to you also for your suggestions. My requirement is for the user to be able to choose one or more of the choices, so an option frame of radio buttons won't do.

I'm afraid I don't understand your second suggestion to form the criteria and append it to the query. What do you mean by append it to the query?

 

[Steve101]

RYankowitz,

Yes, quite right; I should have read the message properly (never was much good at exams because of that!!).

I'm surprised that the query causes such an overhead. I run this query on 4,000 records and its pretty near instantanious (single, simple table).

SELECT Table1.a, Table1.b,
AandB(a,b) AS AandB, AorB(a,b) AS AorB
FROM Table1
WHERE AandB(a,b)=AandB(a,b)
AND AorB(a,b)=AorB(a,b);


Lets have a look at your query. Perhaps it can be better optimised. 5 seconds seems a bit high for that number of records.

If its a multi table query, perhaps it can be re-organised to ensure that the function evaluation is minimised.

Cheers,
Steve

 

[RYankowitz]

Steve,

I believe I'm there now. Along the way I found two interesting bits:

1. The five second performance hit only occurs if I run the query directly. It is even worse if I have the VBE editor open (which apparently causes Access to update the Task Bar at the bottom of the screen each time the function runs). Causing the query to run via the form for which it provides data only takes about one second. Isn't that strange?
2. A mistake I discovered is in not having one of the fields in the table indexed. Doing so eliminated most, but not all, of the hit.
3. More importantly, I've eliminated the entire hit (at least as far as the user sees) by removing the Totals line from the Query grid. It is news to me that simply having that line visible causes Access to evalate the entire query before displaying any results, even if no totals are actually being calculated (all fields were set to Group By). Because Access now displays the first page of results instantly (it still takes some time to show the total number of records returned) the form the query feeds does the same. The practical effect is instant feedback for the user, which is good enough for me.

BTW, for comparison purposes, I'm running this on a machine with -
600 mHz P-III
384 mB RAM
Oodles of free disk space.

Thanks for all the help and effort...

Bob

 

[Steve101]

Bob,

Good work, and some very interesting observations.

(a) One little experiment that I have done in the past, is to place a stop (or a msgbox message) in a function called in a query. Doing this, you can observe how often the function is called when the query is being run; can in fact be several times. I dont remember the details, but I did read an article on it once.

(b) Re your third point; An aggregate query can impose a large overhead, irrespective of whether you actually total any of the rows or not. The operation of "collapsing" the data to your selected GroupBy fields involves sorting EACH column; then collapsing the data. I think that unless you're intending to aggregate any of the GROUP BY fields, you might be better off to use a standard query with the DISTINCT clause to eliminate duplicate returns.

Cheers,
Steve

 

[RYankowitz]

Steve,

a) The article you saw is probably this one from the Microsoft Knowledge base, "Number of Times a Custom Function Runs in a Query," Q210554. I had done what you suggested, and found it met my expectations: the function ran once per record, or in my case, 3,300 times.

b) My problem here was not one of incorrect usage, but one of stupidity. I didn't notice the Totals line was activated, and even when I did, I didn't realize the consequences. Since all fields were set to Group By there was no effect on the query results except for the slowdown.

Another article I found, "How to Optimize Queries in Microsoft Access 2000," Q209126, explains plenty about the subject.

(BTW, I couldn't put the actual URL's here because the Tek-Tips forum mangled them.)

Regards,

Bob

 

[Steve101]

Yes, that was the article;Glad its all solved,
Cheers,
Steve