You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

Combo box controls and list box controls display no value or incorrect values in Access 2003 after you install Office 2003 Service Pack 3

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

SYMPTOMS
Consider the following scenario:
  • You have the following installed:
    • Microsoft Office Access 2003
    • Microsoft Office 2003 Service Pack 3 (SP3)
  • You add a combo box control or a list box control to a form or to a report.
  • You set the Row Source property of the control to an SQL statement.
In this scenario, you encounter one of the following symptoms.

Symptom 1

The control displays no value. However, you expect the control to display values from the field that is returned by the SQL statement.

Symptom 2

The control displays -1 for the Yes value and 0 for the No value. However, you expect the control to display Yes for the Yes value and No for the No value.

These symptoms do not occur in versions of Access 2003 earlier than Access 2003 with Office 2003 SP3.
CAUSE

Cause 1

This problem occurs when one of the following conditions is true:
  • The SQL statement returns a field whose data type is Text, and you have already set a display format for this field.
  • The SQL statement returns a field whose values are from a table that links to an Excel worksheet. Additionally, the cells that contain these values in the Excel worksheet have the Text property.
To work around this problem, see Workaround 1.

Cause 2

This problem occurs when the SQL statement returns a field whose data type is Bit. To work around this problem, see Workaround 2.
WORKAROUND

Workaround 1

To work around this problem, use one of the following methods, depending on the cause of the problem.

Method 1

Remove the display format that you set for this field. To do this, follow these steps.

Note Use this method when the first condition of the "Cause 1" section is true.
  1. Open the table that contains this field in Design view.
  2. Locate the field, and then click the General tab.
  3. In the Format box on the General tab, delete the format that is set.
  4. On the File menu, click Save.
  5. On the File menu, click Close.

Method 2

Update the SQL statement to append "" to the field for the Row Source property. To do this, follow these steps.

Note Use this method when the second condition of the "Cause 1" section is true.

For example, the original SQL statement is as follows:
SELECT FieldName FROM LinkedSpreadsheetName
You update the SQL statement as follows:
SELECT FieldName & "" FROM LinkedSpreadsheetName
Note When you import columns that have the Text property from an Excel worksheet, Access 2003 automatically sets the display format to the @ (at sign) character for the corresponding fields.

Workaround 2

To work around this problem, follow these steps:
  1. Update the SQL statement for the Row Source property to return the appropriate value. For example:
    • The following SQL statement forces the control to display Yes for the Yes value and No for the No value.
      SELECT TableName.YesNoFieldName, IIf([YesNoFieldName]=-1,"Yes","No") AS YN_Value FROM TableName
    • The following SQL statement forces the control to display True for the Yes value and False for the No value.
      SELECT TableName.YesNoFieldName, IIf([YesNoFieldName]=-1,"True","False") AS YN_Value FROM TableName
    • The following SQL statement forces the control to display On for the Yes value and Off for the No value.
      SELECT TableName.YesNoFieldName, IIf([YesNoFieldName]=-1,"On","Off") AS YN_Value FROM TableName
  2. Make sure that the control displays correctly. To do this, set the appropriate value of other properties as indicated in the table that follows these steps.
PropertyValue
Bound Column1
Column Count2
Column Widths0"; 0.5"
RESOLUTION
To resolve this problem, obtain the Access 2003 post-Service Pack 3 hotfix package that is dated December 18, 2007. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
945674 Description of the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Properties

Article ID: 945280 - Last Review: 01/02/2008 19:28:48 - Revision: 2.1

  • Microsoft Office 2003 Service Pack 3
  • kbexpertiseadvanced kbtshoot kbprb KB945280
Feedback