ACC1x: Date/Time Format Not Reflected in List/Combo Box (1.x)

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

A list or combo box based on a Date/Time field does not display the Date or Time format assigned to the underlying field. This is particularly noticeable in a list or combo box control on a form.

CAUSE

Microsoft Access does not have a Format property for list or combo boxes even though the underlying table field has a defined Format property.

RESOLUTION

Use the Format() function in an expression in a query to format the data. Then base the list or combo box on the query field instead of the underlying table field.

For example, define a query named QueryTest. Then instead of entering the name of the underlying Time/Date field (Event Date) as the field on the QBE grid, enter this expression:

   Event Day: Format([event date],"mmmm d"", ""yyyy")

STATUS

This behavior no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a new table with the following fields:

         Field Name         Data Type
         ----------------------------
         Event ID           Counter
         Event Date         Date/Time
   
   

2. Set the Event Date field's Format property to "mmm d, yyy" (without the quotation marks).

3. Save the table as Events. View the table in Datasheet view and enter "8/11/92" (without quotation marks) in the Event Date field. Add a few more records with any valid dates. Note that the dates are displayed correctly.

4. Create a new form based on the Events table. Add the following list box to the form:

         Object: List Box
         ----------------------------
         Control Name:    Event ID
         Control Source:  Event ID
         Row Source Type: Table/Query
         Row Source:      Events
         Column Count:    2
         Column Heads:    Yes
         Column Widths:   0;1
         Bound Column:    1
   
   

5. View the form in Form view. Note that the underlying field's Format property is ignored.