Take Your Web Site to New Levels

Formatting Numbers in the Database Results Wizard

One of the challenges with the Database Results Wizard is that it seems to ignore the number formats that you set up in MS Access. Your lovely currency fields vanish. Your phone numbers become a long sequence of numbers lacking the normal punctuation that helps us recognize a phone number. Your dates and times seem to take the least visually appealing formats.

This tutorial will show you how to correct these challenges. I've created a sample database that has a contact to be used as an example. It includes fields for Name, Phone, Credit Limit, Date and Time. In Access, I set up the table as follows:

Name: Text
Phone: Number, Format (000) 000-0000
CreditLimit: Currency, Format 00.00
Date: Date/Time, Short Date
Time: Date/Time, Long Time

Here is how FrontPage interprets this data:

Name: Bill Jones

Phone: 8005551212

CreditLimit: 450

Date: 1/23/2003

Time: 3:30:00 PM

ContactID: 1

Clearly, this is not what I had in mind. To correct this, we perform a little FrontPage Magic. We already know how to use the Database Results Wizard (DRW) to display a simple list like the one above. Now, we need to teach it to display numeric fields the way WE want them to be formatted.

Work through the DRW, select the fields you would like to choose and then in Step 4 select List - one field per item. Now when you get to Step 5

STOP! DO NOT click the FINISH button!

Take a breath, because here is where the magic happens. I want you to click the BACK button several times until you get to Step 2 (trust me on this).

In Step 2, click the Custom Query button and then the Edit button.

The Custom SQL code will resemble the following:

SELECT * FROM tFormatNumbers

You can view this in the following screen shot:

This essentially means select all the fields from the table tFormatNumbers. The * key is used to select ALL fields in a table. If you want it to show only certain fields, change it to:

SELECT Name, Phone, CreditLimit FROM tFormatNumbers.

In this case, now to format our fields, we change the existing code to:

SELECT *, FORMAT(Phone, '(000) 000-0000') AS fPhone, FORMAT(CreditLimit, '00.00') AS fCreditLimit,
FORMAT(Date, 'MMM DD, YYYY') AS fDate,
FORMAT(Time, 'H:MM AM/PM') AS fTime FROM tFormatNumbers

Again, here is a screen shot:

Basically, what you have done is added a way for FrontPage to take your fields, format them, and then give them a new name. The command:

FORMAT(Phone, '(000) 000-0000') AS fPhone

will create a NEW field called fPhone that will be formatted correctly. Here is what the output looks like.

fPhone: (800) 555-1212

fCreditLimit: 450.00

fDate: Jan 23, 2003

fTime: 3:30 PM

ContactID: 1

Name: Bill Jones

Phone: 8005551212

CreditLimit: 450

Date: 1/23/2003

Time: 3:30:00 PM

You can see your original Phone, CreditLimit, Date and Time fields with their incorrect formatting, but there are four new fields fPhone, fCreditLimit, fDate and fTime that ARE formatted correctly.

The solution for your FrontPage webs is to simply delete the incorrectly formatted fields and to use the newly created fields for your web page.

If you want to format your currency even further, you can use the following syntax to add a dollar sign AND a comma for values over $1,000.00:

FORMAT(CreditLimit,'$#,##0.00') AS fCreditLimit

It's like Magic!