FrontPage Tips by Ward Cameron Enterprises
Take Your Web Site to New Levels

Using OR Along with AND in the Criteria Builder of the DRW

This tip assumes that you are comfortable with using the Database Results Wizard (DRW) in Microsoft FrontPage. This powerful wizard allows you to display data from your favorite database (in this case MS Access 2002) in any web browser.

When using the Database Results Wizard (DRW) in Microsoft FrontPage, you have the option of limiting the results displayed through the use of criteria. Unfortunately, if you use a combination of OR along with AND, you may not get the results you expect. When you combine these two criteria, the DRW performs the AND operation, before performing the OR operation.

The following tutorial shows the problems and showcases how you can correct the error.

Step1: Create the Database Results Wizard Page
Step 2: Limit the Results using the OR Statement
Step 3: Limit the Results using the OR and AND Statements
Step 4: Correct the Problem

Step 1 Create the Database Results Wizard Page

This example uses the Northwind sample database that comes free with Microsoft Access. In the following table, I have created a DRW which shows all the products  in the Northwind database sorted by CategoryID and then by ProductName.

Step 2: Limit the Results using the OR Statement

If we assume that you would like to limit the results to particular records, such as those with a category ID of 1 or 2, the list will become much shorter. To do this, in the DRW on screen three, you will see the following screen.

Click on the "More Options" button and on the following screen click on "Criteria".

When you see the following Criteria Screen, click the "Add" button.

This will open the Modify Criteria dialog. In this screen, work through adding CategoryID Equals 1 (deselect the "Use this search form field" option). Make sure the "And/Or" option is set to "Or". Then add a second criteria where the CatergoryID Equals 2.

 Select "OK", and the Criteria screen should look as follows.

The following screen will then show only those products with a CategoryID of 1 OR 2.

Step 3: Try to Limit the Results using the OR and AND Statements

Finally, let's add another filter. If we now want to limit the list ever farther, say to those with a category ID of 1 OR 2 AND with less than 50 Units in Stock, the results will be incorrect. This is due to the fact that FrontPage will do the AND operation before the OR operation. As a result, the following list will instead filter the items in the following manner:

1. First it will filter out those items with a CategoryID of 2 AND with LESS THAN 50 UnitsInStock
2. Then it will find those products with a CategoryID of 1. These items will not be limited based upon the LESS THAN 50 criteria.

The following list based upon this filter will showcase the problems.

Step 4: Correct Problems Using the OR and AND Statements

Let's look at how the criteria have were originally set up. If on the DBW on screen three, click the "More Options" button, and then the "Criteria" button, you should see the following graphic.

This shows that you have set up the criteria correctly. It should look for those records with a CategoryID of 1 OR 2 AND with LESS THAN 50 UnitsInStock.

Remember, this is the fault of FrontPage. You did your job, now we just need to help FrontPage a little bit.

To correct the problem, go back into your DRW. Go back to screen two of the DRW. It should look like the following screen shot.

Now, select "Custom Query" and "Edit". This is what you should see:

The problem is that FrontPage does not tell the DRW what order to do the OR and AND operations.

We need to add some brackets to set the operation order. In the custom SQL screen, add a bracket around the OR operation. This will correct the problem. You will see a second left bracket beside CategoryID and a new right bracket after CategoryID=2. This will tell FrontPage to do the OR operation first, and then do the AND operation.

Now that we have corrected the problem, the following DRW will show ONLY those records with a SupplierID that is equal to 1 OR 2 AND with UnitsInStock that are less than 50.

The only problem remaining with this solution is that you will now display all of the columns in your database table, rather than just the four columns we originally wanted. You can solve this by simply deleting the unwanted columns. Another solution is to change our Custom SQL code to specify the columns we want to display.

Here is how the SQL would look:

SELECT ProductName, UnitsInStock, CategoryID, SupplierID FROM Products WHERE ((SupplierID = 1 OR SupplierID = 2) AND UnitsInStock < 50) ORDER BY CategoryID ASC,ProductName ASC

In this example, rather than the default SELECT *, we add the names of the fields we want shown in the order we want them displayed.

Many thanks to Texas Web Developers for suggesting this tutorial