Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

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

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.

↑ Back to the top

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.

↑ Back to the top

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"

↑ Back to the top

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

↑ Back to the top

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

↑ Back to the top

Keywords: kbexpertiseadvanced, kbtshoot, kbprb, kb, kbarchive

↑ Back to the top

Article Info
Article ID : 945280
Revision : 3
Created on : 4/23/2018
Published on : 4/23/2018
Exists online : False
Views : 198