Microsoft KB Archive/161331

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

Article ID: 161331

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q161331



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



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



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.

 Open the sample database Northwind.mdb.  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: #####  Save the InputMasks table, and then open it in Datasheet view.  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. </li> Close the table.</li>  Create the following new macro called TestXLFormat:

<pre class="fixed_text">     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 </li> Saved the macro, and then run it.</li> 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.</li> 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.</li></ol>

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.

<div class="references_section">