Case Study Working with Very Complex Criteria

Your clients so loved the "Create a Customer"report,they hired you to write a new report. In this case, they could select any customer, any product, any region, or any combination of them.You can quickly adapt the frmReport userform to show three list boxes, as shown in Figure 11.13.

In your first test, imagine that you select two customers and two products. In this case,your program has to build a 5-row criteria range,as shown in Figure 11.14.This isn't too bad.

This gets crazy if someone selects 10 products,all regions but the house region,and all customers except the internal cus-tomer.Your criteria range would need unique combinations of the selected fields.This could easily be 10 products times 9 regions times 499 customers,or more than 44,000 rows of criteria range.You can quickly end up with a criteria range that spans thousands of rows and three columns. I was once foolish enough to actually try running an Advanced Filter with such a criteria range. It would still be trying to compute if I hadn't rebooted the computer.

The solution for this report is to replace the lists of values with a formula-based condition.

Figure 11.13

This super-flexible form lets clients run any types of reports that they can imagine. It creates some nightmarish criteria ranges, unless you know the way out.

Figure 11.13

This super-flexible form lets clients run any types of reports that they can imagine. It creates some nightmarish criteria ranges, unless you know the way out.

Customer F to duel__

Figure 11.14

This criteria range returns any records where the two selected customers ordered any of the two selected products.

Customer F to duel__

0 0

Post a comment