ACC1x: Combo or List Box Column Not Formatted

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

A column in a combo or list box displays information without the formatting you specified for the field in the underlying table or query.

CAUSE

Microsoft Access does not use the format specified in the Format property for a field in a table or query when the data is being displayed in a combo or list box. Information contained in a table field will be displayed with the default format for the data type when the data is being displayed in a combo or list box. For example, Date/Time fields will display with the General Date format, and numbers will display with the General Number format.

RESOLUTION

Base the combo or list box on a query, and format the field using the Format() function in a calculated query. The following example demonstrates how to use the Format() function in a query to format a field:

1. Open the sample database NWIND.MDB.

2. Create the following new query based on the Orders table:

         Query: Order Date Query
         -------------------------------------------------------------
   

         Type: Select Query
            Field: New Order Date: Format([Order Date], "Medium Date")
            Table: Orders
   
   

3. Run the query. Note that the date is formatted with the Medium Date format.

4. Save the query as Order Date Query, and then close the query.

5. Create a new, blank, unbound form. Add a combo box with the following properties:

         Combo Box: Order Dates
         ---------------------------
   

         RowSourceType: Table/Query
         RowSource: Order Date Query
         ColumnCount: 1
   

6. View the form in Form view.

STATUS

This problem no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database NWIND.MDB.

2. Create the following new query based on the Orders table:

         Query: Order Date Query
         -----------------------
   

         Type: Select Query
         Field: Order Date
            Table: Orders
   
   

3. Run the query. Note that the date is formatted with the Medium Date format.

4. Save the query as Order Date Query, and then close the query.

5. Create a new, blank, unbound form. Add a combo box with the following properties:

         Combo Box: Order Dates
         ---------------------------
         RowSourceType: Table/Query
         RowSource: Order Date Query
         ColumnCount: 1
   

6. View the form in Form view.

REFERENCES

Microsoft Access "User's Guide," version 1.0, pages 126-128

Microsoft Access "User's Guide," version 1.1, pages 130-132

Microsoft Access "Language Reference," version 1.0, pages 202-219