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

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

REFERENCES