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

Using the iif Statement with Microsoft Access

Microsoft Access is an amazingly powerful tool. The query design window provides an endless way to wade through the most extensive database to filter out only the most relevant data.

One of the single most powerful query tools is the iif statement. Quite simply, it allows you to compare a number of values and have the query return specific results based upon this comparison.

Here is the syntax:

iif(test,True,False)

Basically, what this does is test a condition, for instance [Day] = "Monday" or [Quantity] < 50, and then perform one of two operations depending upon the result of this test.

Here is another example. Place the following line into a blank line in the query design window:

Image: iif([PhotoName1] = "","no.gif","yes.gif")

In this case, it looks at the PhotoName1 field. If it is blank (signified by two double quotes side-by-side), than it will display the text no.gif, otherwise it will display yes.gif. It will place this value into a field called "Image". This example is useful when you want to display a specific graphic file if there are image names stored in the PhotoName1 field.

In the Query Design Window, enter the above text into a blank line as follows:

If you are more comfortable working with the Expression Builder, right click on the Field line and select "Build"

Type the expression into the Expression Builder and click OK..

There is no end to the number of ways the iif statement can add functionality to your web site. You can compare values based upon just about any imaginable criteria. Examples include:

• Equals or Not Equal to (= or <>)
• Less or Greater Than (< or >)
• Less Than or Equal to (<=)
• Greater Than or Equal to (>=)
• Between
• OR
• AND
• NOT
• IsNull
• Date comparisons - be sure to surround the date value with the # sign (e.g. [date] = #01/01/2003#) or your comparisons will not work

Once you begin to harness the power of the iif statement you will find yourself using it more and more.

Reasons to Use the iif Statement

• Linking Fields

• Flagging Inventory Reorder Levels

• Indicating Image Files

• Customers in certain States

• Products from certain suppliers

• Order Confirmations

• The list goes on and on

Linking Fields

This may not the the most common way to use the iif statement, but once you use it this way the first time, you'll find yourself incorporating iif statements on almost all of your queries.

In this example, we'll use it to help us link two fields, such as first name and last name in a variety of ways.

Linking fields can cause problems because some records may only contain one part of the data. People may only input their first or last name. If you have fields that list inventory parts, some parts may only have one name, such as hammer [LastName]. Others will have two names such as 3/4 inch [FirstName] plywood [LastName].

Normally you could link these in one of two ways:

Name: [FirstName] & " " & [LastName]

This would create a linked name like "John Smith" or "3/4 inch plywood". The two double quotes with a space between them provides the space between John and Smith.

IndexName: [LastName] & ", " & [FirstName]

In this case, you would end up with "Smith, John" or "Plywood, 3/4 inch". This layout is ideal for creating indexes or grouping parts in a catalog.

So what's the problem? What if you are dealing with hammers, or someone just enters their first name, John? This rigid technique will give you poorly formatted results. In case one, you would get either "John " (extra space) or " hammer" (space before the word). Even worse though, in the second instance you would get ", John" (comma and space before name) or "hammer, " (comma and space after name).

The iif statement can solve these problems by providing specific actions for each possible case. Here's one way of rewording the first example:

Name: iif([LastName] = "", [FirstName],[FirstName] & " " & [LastName])

Here we are saying if the LastName is blank, than just use the first name, otherwise link both together.

In the second situation we would rework it this way:

IndexName: iif([FirstName] = "", [LastName], [LastName] & ", " & [FirstName])

Here, we've stated if FirstName is blank, than just enter the LastName, otherwise list LastName, FirstName. Again, it helps us correct formatting problems.

Flagging Inventory Reorder Levels

In your table, have a field with a minimum inventory level, often called a reorder point. In your query you can use the following statement:

Reorder: iif([Quantity] <= [ReorderPoint], "Stock Low","")

Here, you will get a message "Stock Low" if the Quantity is Less Than or Equal to the Reorder Point. Otherwise, you will get no message.

Indicating Image Files

Some sites like to have a little camera icon beside a category name to indicate that images are available. To do this, create two images.

First create a tiny blank transparent gif called no.gif. Use this images name as the default name for your image fields in your database.

Now, create a small camera gif and call it yes.gif. you can then use the following statement in your query:

Image: iif([PhotoName] = "no.gif","no.gif","yes.gif")

Replace [PhotoName] with the name of the field you place your image names into. This will add a query field called Image. Each record will have one of two names no.gif or yes.gif.

Use this column to display the image. If there is no image in the PhotoName field, you will not see any image because it will display your blank transparent gif, no.gif, essentially an invisible image. This prevents you from getting the ugly symbol that is displayed when the browser cannot find an image.

If the Image field in your database has any other value other than "no.gif", than you will see the camera image and your users will know that images are available.

Customers in Certain States

Perhaps you want to flag customers from Hawaii or Alaska. Use the following statement:

NonContiguous: iif([State] = "Alaska" OR [State] = "Hawaii", "Extra Shipping","")

In this case, you will get a message, "Extra Shipping" for customers in Hawaii or Alaska. This would reflect the additional costs of shipping to these more distant outposts.

Products from Certain Suppliers

You may need to pull out products from certain suppliers. Use the following:

SupplierSearch: iif([Supplier] = "Bill's Furniture", "yes","no")

This will place the text string "yes" in the SupplierSearch field if the product supplier is "Bill's Furniture".

Order Confirmations

If you want to display a list of orders with a message indicating whether they have been confirmed, use the following statement.

OrderConfirmation: iif([Confirmed] = "Yes", "Order Confirmed", "Order Not Confirmed")