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.

Nulls replaced with next field's data when You export to Microsoft Excel


View products that this article applies to.

This article was previously published under Q294410
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top


Symptoms

When you export data to the Microsoft Excel file format, null values are replaced with the data that should be in the next column in the resulting spreadsheet.

↑ Back to the top


Cause

This behavior occurs when all of the following conditions are true:
  • The first two columns are numeric and contain integer data.
  • The third column allows null values.
  • The fourth column is the same numeric data type as the first two.
  • At least one record contains the exact same values in the first two columns. All subsequent records meeting the criteria in the first three items demonstrate the problem.
  • You use the Export command or the TransferSpreadsheet method to export data.
  • You are using the Excel ISAM driver, Msexcl40.dll, version 4.0.4331.3.

↑ Back to the top


Resolution

Use one of the following methods to resolve this problem:
  • This problem has been fixed in the latest edition of the Jet Service Pack. For additional information about how to obtain the latest version of the Jet 4.0 database engine, click the following article number to view the article in the Microsoft Knowledge Base:
    239114� How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine
  • Set the Export as Formatted option to True. To do so, click to select the Save formatted check box in the Export Table '<name>' To dialog box.
  • Use a query that converts the nulls to 0 or zero-length strings rather than exporting the values directly from the table. For example, if the field Text1 contains null values, rather than Text1, your query column would be MyText: Nz(Text1, " ").
  • Use the OutputTo action rather than the Export command on the File menu or the TransferSpreadsheet method.
  • Change the table design so that the column containing null values allows zero-length strings or uses a zero-length string or 0 as the default value (or both). Use an update query to replace all null values with the appropriate value.

↑ Back to the top


Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Jet 4.0 SP6.

↑ Back to the top


More information

Steps to Reproduce the Behavior

  1. Create a new blank database in Access.
  2. Create a new table in Design view
  3. Add the following fields:
    Collapse this tableExpand this table
    Field Name:Data Type:
    nbr1Long Integer
    nbr2Long Integer
    text1Text(50), Allow Zero Length = No
    nbr3Long Integer

  4. Do not add any indexes. Save the table as tblRepro, and click No when you are prompted to create a primary key.
  5. Open the table in Data Sheet view. Enter a record with the following values: 1, 1, (Null), 3 (where null indicates no value is added for the column).
  6. Move the mouse pointer to the next record to commit the changes.
  7. On the File menu, click Export.
  8. Click the arrow in the Files of Type box, click Excel 97-2000 in the list, and then save the file as C:\My Documents\tblRepro.xls.
  9. Open C:\My Documents\tblRepro.xls. Note that the resulting spreadsheet contains the following values:
    Collapse this tableExpand this table
    nbr1nbr2text1nbr3
    113(blank)

    The resulting spreadsheet should have the following values:
    Collapse this tableExpand this table
    nbr1nbr2text1nbr3
    11(blank)3

↑ Back to the top


Keywords: KB294410, kbbug, kbarttypedocerr, kbqfe, kbhotfixserver

↑ Back to the top

Article Info
Article ID : 294410
Revision : 11
Created on : 1/31/2007
Published on : 1/31/2007
Exists online : False
Views : 338