DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 945280 - Last Review: January 2, 2008 - Revision: 2.1

On This Page

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.
Collapse this tableExpand this table
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  (http://support.microsoft.com/kb/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.

APPLIES TO
  • Microsoft Office 2003 Service Pack 3, when used with:
    • Microsoft Office Access 2003
Keywords: 
kbexpertiseadvanced kbtshoot kbprb KB945280
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support