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

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.