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

Working with Date Fields in Your Database Forms

If there is one thing that confounds web developers, it's the challenge of providing a controlled way for site users to input date related information.

I know, you could just put in a text box, but then you lose control over how they enter date values.

North Americans will enter dates in the month/day/year format. Europeans are accustomed to entering dates in the day/month/year format. Others will insist on entering year/month/day, even year/day/month.

What's a poor database to do?

How can you control each site visitor's built in tendency to do their own thing?

As a web designer, you need to solve this problem. I struggled with this challenge for more than a year

A Failed First Attempt

My first solution was to break the date up into three fields, one each for month, day and year. These were individually sent to the database as number fields.

Later, after each field had been submitted to my Access database, I had to manually glue them together in a query using complex Access iif Statements.

I went back to the web to find alternate solutions.

I found numerous pop-up calendar controls. Unfortunately, I still haven't managed to get a single one of them to work.

Eureka...I have it

So what's the solution?

You'll laugh when I tell you!

I stumbled upon the solution while reading posts at one of the many discussion forums that help weary webmasters like me. And there it was. It was so simple.

My original solution was so close. I was right on the money putting in three fields, one each for Month, Day and Year. The secret lies in naming all three with the same name (for example "Date").

The magic occurs behind the scenes. When you submit multiple values with the same name, FrontPage will combine them prior to sending them to the database.

As an example, if you select May 1, 2004, FrontPage will take the three values and turn them into 5,1,2004 and when that value is sent to Access, it is recognized as a valid date.

It's not a miracle - It's FrontPage Magic!

Using this technique with updatable forms

To take this one step further, I needed to find a way to use this same technique in an updatable form. When I developed my online database applications, the first step was creating the main form to add a record to the database. We've now accomplished that task.

Now what happens when you want to edit that record. You can use the same technique, but you need to add a few steps to set up the form.

Once you have entered a date to your database, you will need to disassemble it to create the three field values of month, day and year. I accomplished this by creating a query and adding three new fields, each of which displayed part of the date value.

To do this, create a blank query and add all of the values from your original table. Now, in a blank column of the query design window type the following:

Month:Format([Date],"mm")

This will create a new field in the query called "Month", formatted to show only the two digit month value. Now, let's create two more values:

Day:Format([Date],"dd") and
Year:Format([Date],"yyyy")

You now have added fields for Month, Day and Year to the query. When we create the update form using the techniques in the Edit Records Tutorial we'll add an identical date field from the first step of this tutorial. The only difference is an additional value added to the top level of each drop-down list.

Copy the Date drop-down fields from your original Add Record form. Double click on the Month Drop-down and click the add value button. Under choice type the following:

<%=FP_FieldHTML(fp_rs,"Month")%>

It should look like the image to the left. Be sure to deselect Specify Value and to make sure Selected is highlighted.

Do this again for each of the other two fields. You've now created a series of three drop-down lists that will automatically display the currently stored values for month, day and year. When the form is loaded, you have the opportunity to leave the current values or change it to one of the other values. This is way cool once you put it into action.

Setting a Default Date

If you choose to use a text field to enter dates, you can set the default date in a date field as the current date, into a blank form place a text field. Now right click and select form field properties. Enter the following code into the Initial Value Field: <%=date()%>

Many thanks to Hisham Hhammash for his assistance with this tutorial.