ACC: Number Field with Input Mask Appears as Text in MS Excel

Last reviewed: June 4, 1997
Article ID: Q161331
The information in this article applies to:
  • Microsoft Access, versions 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you export a table to Microsoft Excel using the OutputTo macro action or a Visual Basic method, Number fields that have an input mask appear as Text fields in Microsoft 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/Export command on the File menu. If you only want the data in Microsoft Excel to look the same as it does in Microsoft Access, you can use OutputTo.

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. Open the sample database Northwind.mdb.

  2. Create the following new table in Design view. You do not need to create a primary key:

          Table: InputMasks
          ------------------------
          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: #####
    
    

  3. Save the InputMasks table, and then open it in Datasheet view.

  4. Add the following records to the InputMasks table:

          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.
    
    

  5. Close the table.

  6. Create the following new macro called TestXLFormat:

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

  7. Saved the macro, and then run it.

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

  9. Click in the B2 cell, and then click Cells on the Format menu. Note that the Number tab shows a custom format, #####, that makes the Text cells appear as Numbers.

If you export the InputMasks table to Microsoft Excel format using the Save As/Export command on the File menu, the numeric integrity of your data is preserved.

REFERENCES

For more information about the OutputTo action or method, search the Help Index for "OutputTo action" or "OutputTo method."


Keywords : IsmExl5 IsmOthr kbinterop
Version : 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbprb


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 4, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.