Take Your Web Site to New Levels

When you generate pages using Microsoft FrontPage's Database Results Wizard, are you troubled by fields that contain no data insisting on taking up space on your web page? Here's how to correct this problem.

This is a common question on FrontPage related websites. While undoubtedly there are other solutions that use complex coding, a better solution involves solving this problem in your Microsoft Access query prior to sending the data to the Database Results Wizard (DRW).

Here is an example. You have a DRW page that displays a company contact on one line, and the name of the company on the second line. Unfortunately, just because a certain record may not have any data to display, for example you do not have a contact name, the blank data will still take up a line on the page, pushing your other data farther down the page.

If this is a common problem, try using an iff statement in your Access query to create a new field that will display only company information if there is no contact name. If there IS a contact name, it will display the data AND add a carriage return so the company name appears on the following line.

To illustrate this problem, I've created a database and added several fields, including [Name] and [CompanyName]. I've entered two records into the table, one that includes BOTH a Name and a Company Name and one that only includes a company name.

After importing the database to FrontPage, I used the Database Results Wizard to display the [Name] and [CompanyName] fields. In Step 4 of the DRW I selected "List - one field per item" as the formatting option. I also turned the display of field labels off. Here is how it looked in FrontPage:

Here is how the page appears online:

Ward Cameron

Ward Cameron Enterprises

Bill Smith & Associates

Unfortunately, I did not have a company contact name for the second record so it does not display any data...

...BUT it still takes up a line on the page, leaving a conspicuous blank.

If you look at your data BEFORE you create your DRW in FrontPage, you can often solve this problem in Access, rather than needing to deal with it in FrontPage.

The solution to this formatting challenge rests with a fabulous feature called the iif statement. This function allows us to display one set of data if a condition is true OR another if it is false. In this case, we want to display the Name on line one, followed by the Company Name on line two. If there is no Name, then we want ONLY the company name displayed. Here's how to accomplish this.

In your Access query design window make sure that you currently have both the [Name] and [CompanyName] fields included. Your query design window will look like the following:

When you preview your query, it will look like the following:

You can see the missing [Name] data in the second record (Bill Smith & Associates). Switch back to design view and click on the Field line in a blank column. Right click in this cell and select build. This will open the Expression Builder.

Type the following into the Expression Builder window:

Contact: IIf(IsNull([Name]),[CompanyName],[Name] & " <BR> " & [CompanyName])

Be sure to leave all the spaces in place. I've used yellow highlights to show the important space locations.

This will create a NEW field in the query called [Contact]. IF the [Name] field is blank (Null), it will display only the [CompanyName] field,  otherwise it will display the [Name] field followed by [CompanyName] and separated by  the HTML code <BR>. This code represents a line break in HTML. Later, we'll tell FrontPage to transform this code into a carriage return to place the Company Name onto a new line.

Click OK and then preview your query. It should look something like the following:

We'll put the <BR> code into play in a moment. For now, insert a DRW onto your page, but only include the Contact field, rather than the Name and Company Name fields. It will look like the following:

Here is how the data appears when viewed online:

Ward Cameron <BR> Ward Cameron Enterprises

Bill Smith & Associates

This is getting closer. There is still the problem of the <BR> code. In this step, we'll turn that into a carriage return that will put the Company Name on a new line.

To accomplish this, double click on the <<Contact>> field name within your Database Results Region. Alternatively, you can right click on the field and select "Database Column Value Properties" from the context menu.

This will open the Database Column Value Properties window. Select the option: "Column value contains HTML". This will tell the DRW to interpret any HTML code that may be included in the data field. In this case, it will take the <BR> tag and turn that into a new paragraph. Now, if you have no data for your Name field, the DRW will show only the Company Name. If you DO have Name data, then it will display the name, add a line break and then display the Company Information on the new line. Check it out below:

Ward Cameron
Ward Cameron Enterprises

Bill Smith & Associates

Is this cool or what? You are no longer are a slave to blank data records. And all it took was a little FrontPage Magic!