ACC2000: Number Field with Input Mask Appears As Text in Excel (207883)


The information in this article applies to:

  • Microsoft Excel 2000
  • Microsoft Access 2000
This article was previously published under Q207883
Moderate: Requires basic macro, coding, and interoperability skills.

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

SYMPTOMS

When you export a table to Microsoft Excel using the OutputTo macro action or OutputTo Visual Basic method, Number fields that have an input mask appear as Text fields in Excel.

CAUSE

When a field in a table uses an input mask, Microsoft Access outputs the data in a custom format. The custom format causes the indexed sequential access method (ISAM) driver for Microsoft Excel to treat the field as Text.

RESOLUTION

If you want to maintain your data as a Number field, use the Save As or Export command on the File menu. If you only want the data in Excel to look the same as it does in Access, you can use the OutputTo action or OutputTo method.

MORE INFORMATION

Steps to Reproduce Behavior

NOTE: The following example uses the OutputTo action in a macro. The same results apply if you use the OutputTo method of the DoCmd object in Visual Basic code.
  1. Create a new blank database.
  2. In the Database window under Objects, click Tables, and then click New.
  3. Click Design View, and then click OK.
  4. Create the following new table. You do not need to create a primary key:

    Field Name: 99999 text
    Data Type: Text
    Input Mask: 99999

    Field Name: 99999 number
    Data Type: Number
    Input Mask: 99999

    Field Name: 00000 text
    Data Type: Text
    Input Mask: 00000

    Field Name: 00000 number
    Data Type: Number
    Input Mask: 00000

    Field Name: ##### text
    Data Type: Text
    Input Mask: #####

    Field Name: ##### number
    Data Type: Number
    Input Mask: #####

  5. Save the table as InputMasks, and then open it in Datasheet view.
  6. Add the following records: 
       99999   99999    00000   00000    #####   #####
   Text    Number   Text    Number   Text    Number
   ------------------------------------------------
   12345   12345    12345   12345    12345   12345
   01201   01201    01201   01201    01201   01201
   1 2 3   123      12300   12300    1 2 3   123
    Note the way the data is aligned in the Text and Number fields, and note that the second row of data drops the leading zero in the Number fields, but not in the Text fields.
  7. Close the table.
  8. In the Database window under Objects, click Macro, and then click New.
  9. Create the following new macro:

    Action: OutputTo
    Object Type: Table
    Object Name: InputMasks
    Output Format: Microsoft Excel (*.xls)
    Output File: c:\My Documents\InputMasks.xls
    Auto Start: Yes

  10. Save the macro as TextXLFormat, and then run it.
  11. When Microsoft Excel starts and displays the spreadsheet, the alignment and the appearance of the data is the same as it is in Microsoft Access.
  12. Click the B2 cell, and then click Cells on the Format menu. Note that the Number tab shows a Custom category with the format "#####" and that the text in these cells appears as numbers.
If you export the InputMasks table to Microsoft Excel format using the Save As or Export command on the File menu, the numeric integrity of your data is preserved.

REFERENCES

For more information about the OutputTo action, click Microsoft Access Help on the Help menu, type outputto action in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the OutputTo method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type outputto method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kbinterop kbprb KB207883
©2004 Microsoft Corporation. All rights reserved.