Select the product you need help with

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

Article ID: 945280 - View products that this article applies to.

Expand all | Collapse all

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 | Give Feedback

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 | Give Feedback

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.

Open the table that contains this field in Design view.
Locate the field, and then click the General tab.
In the Format box on the General tab, delete the format that is set.
On the File menu, click Save.
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:

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
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.

Collapse this tableExpand this table

Property	Value
Bound Column	1
Column Count	2
Column Widths	0"; 0.5"

Back to the top | Give Feedback

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

(http://support.microsoft.com/kb/945674/ )

Description of the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007

Back to the top | Give Feedback