Microsoft KB Archive/207883

= ACC2000: Number Field with Input Mask Appears As Text in Excel =

Article ID: 207883

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Access 2000 Standard Edition

-



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.



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.  Create a new blank database. In the Database window under Objects, click Tables, and then click New. Click Design View, and then click OK. 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: #####

 Save the table as InputMasks, and then open it in Datasheet view.</li>  Add the following records: <pre class="fixed_text">  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> In the Database window under Objects, click Macro, and then click New.</li> 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

</li> Save the macro as TextXLFormat, 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 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.</li></ol>

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.

<div class="references_section">