I hope someone can help with this...

I have data in Access and it displays well in all reports.  The data I am 
dealing with has part numbers.  These part numbers often are in a format 
similar to:

073D11
073E05
......

When I export a product list to excel, it is changing many (a LOT!) of the 
part numbers to scientific format.  Example:

073D10 becomes 730000000000 (which appears as 7.3E +11)
073E11 becomes 7300000000000 (which appears as 7.3E + 12)

How can I export o Excel and retain the proper number formats (effectively 
as text)?  I have searched through the Microsoft site and found it very 
unhelpful  I found article http://support.microsoft.com/kb/214233  and it 
tells me that Excel does this.  Yes, I know it does this.  Under the section 
"Retaining Number Formats When You Import Text Files or Use Parse", it says 
the following:

"When you import a text file into Microsoft Excel or use the Text to Columns 
command (located on the Data menu) to place text entries in separate 
columns, Microsoft Excel applies number formats to the data if the file 
contains entries similar to those mentioned in this article."

This doesn't tell me how to avoid this reformatting.

Can anyone help?

Stephen 

In the query that is used for the data export, use a calculated field in 
place of the real field, and prepend an ' character to the value:

SELECT FieldName1, FieldName2,
"'" & FieldNameWithLeadZero AS NewFieldNameWIthLeadZero
FROM TableName;

EXCEL will interpret the leading ' character to mean that the value is text.

-- 

        Ken Snell
<MS ACCESS MVP>

"Steve" <webmail@thesecongroup.com> wrote in message 
news:OHTUUQ%23hHHA.588@TK2MSFTNGP06.phx.gbl...
>I hope someone can help with this...
>
> I have data in Access and it displays well in all reports.  The data I am 
> dealing with has part numbers.  These part numbers often are in a format 
> similar to:
>
> 073D11
> 073E05
> .....
>
> When I export a product list to excel, it is changing many (a LOT!) of the 
> part numbers to scientific format.  Example:
>
> 073D10 becomes 730000000000 (which appears as 7.3E +11)
> 073E11 becomes 7300000000000 (which appears as 7.3E + 12)
>
> How can I export o Excel and retain the proper number formats (effectively 
> as text)?  I have searched through the Microsoft site and found it very 
> unhelpful  I found article http://support.microsoft.com/kb/214233  and it 
> tells me that Excel does this.  Yes, I know it does this.  Under the 
> section "Retaining Number Formats When You Import Text Files or Use 
> Parse", it says the following:
>
> "When you import a text file into Microsoft Excel or use the Text to 
> Columns command (located on the Data menu) to place text entries in 
> separate columns, Microsoft Excel applies number formats to the data if 
> the file contains entries similar to those mentioned in this article."
>
> This doesn't tell me how to avoid this reformatting.
>
> Can anyone help?
>
> Stephen
> 

Ken -

The report I am using now is simply a report from Access.  Then I use the 
Access command button "Export To Excel" to export the report to an Excel 
worksheet.  I am not using any SQL query to "export" the data.  The SQL 
Query is sed only to generate an Access report.

Is the query you are instruction me to use initiated in "generating the 
access report", or "exporting the access report to Excel"?  If it is the 
former, then I am confused: the Access Report would display  '073E11 as the 
code, right?  If you are referring to the latter, then I am still confused 
because I am not using any SQL query to "export" the data.  As mentioned 
above, I am using the built-in Access command button to "Export to Excel".

Either way, I am confused.  Please help me with additional explanation?

Stephen
"Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
news:OiDbk6$hHHA.4976@TK2MSFTNGP03.phx.gbl...
> In the query that is used for the data export, use a calculated field in 
> place of the real field, and prepend an ' character to the value:
>
> SELECT FieldName1, FieldName2,
> "'" & FieldNameWithLeadZero AS NewFieldNameWIthLeadZero
> FROM TableName;
>
> EXCEL will interpret the leading ' character to mean that the value is 
> text.
>
> -- 
>
>        Ken Snell
> <MS ACCESS MVP>
>
> "Steve" <webmail@thesecongroup.com> wrote in message 
> news:OHTUUQ%23hHHA.588@TK2MSFTNGP06.phx.gbl...
>>I hope someone can help with this...
>>
>> I have data in Access and it displays well in all reports.  The data I am 
>> dealing with has part numbers.  These part numbers often are in a format 
>> similar to:
>>
>> 073D11
>> 073E05
>> .....
>>
>> When I export a product list to excel, it is changing many (a LOT!) of 
>> the part numbers to scientific format.  Example:
>>
>> 073D10 becomes 730000000000 (which appears as 7.3E +11)
>> 073E11 becomes 7300000000000 (which appears as 7.3E + 12)
>>
>> How can I export o Excel and retain the proper number formats 
>> (effectively as text)?  I have searched through the Microsoft site and 
>> found it very unhelpful  I found article 
>> http://support.microsoft.com/kb/214233  and it tells me that Excel does 
>> this.  Yes, I know it does this.  Under the section "Retaining Number 
>> Formats When You Import Text Files or Use Parse", it says the following:
>>
>> "When you import a text file into Microsoft Excel or use the Text to 
>> Columns command (located on the Data menu) to place text entries in 
>> separate columns, Microsoft Excel applies number formats to the data if 
>> the file contains entries similar to those mentioned in this article."
>>
>> This doesn't tell me how to avoid this reformatting.
>>
>> Can anyone help?
>>
>> Stephen
>>
>
> 

I assume that you're using a query as the report's Record Source. Add the 
calculated field I suggested to that query. Then try the export method for 
the report. You should get that field as an additional column in the excel 
export. You may or may  not need to put a control on the report that is 
bound to this calculated field -- quite honestly, I don't use the export to 
EXCEL option because it doesn't provide me with any benefits/use, so I am 
not familiar with the nuances of using this feature.

-- 

        Ken Snell
<MS ACCESS MVP>


"Steve" <webmail@thesecongroup.com> wrote in message 
news:eaFYWtBiHHA.4132@TK2MSFTNGP05.phx.gbl...
> Ken -
>
> The report I am using now is simply a report from Access.  Then I use the 
> Access command button "Export To Excel" to export the report to an Excel 
> worksheet.  I am not using any SQL query to "export" the data.  The SQL 
> Query is sed only to generate an Access report.
>
> Is the query you are instruction me to use initiated in "generating the 
> access report", or "exporting the access report to Excel"?  If it is the 
> former, then I am confused: the Access Report would display  '073E11 as 
> the code, right?  If you are referring to the latter, then I am still 
> confused because I am not using any SQL query to "export" the data.  As 
> mentioned above, I am using the built-in Access command button to "Export 
> to Excel".
>
> Either way, I am confused.  Please help me with additional explanation?
>
> Stephen
> "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
> news:OiDbk6$hHHA.4976@TK2MSFTNGP03.phx.gbl...
>> In the query that is used for the data export, use a calculated field in 
>> place of the real field, and prepend an ' character to the value:
>>
>> SELECT FieldName1, FieldName2,
>> "'" & FieldNameWithLeadZero AS NewFieldNameWIthLeadZero
>> FROM TableName;
>>
>> EXCEL will interpret the leading ' character to mean that the value is 
>> text.
>>
>> -- 
>>
>>        Ken Snell
>> <MS ACCESS MVP>
>>
>> "Steve" <webmail@thesecongroup.com> wrote in message 
>> news:OHTUUQ%23hHHA.588@TK2MSFTNGP06.phx.gbl...
>>>I hope someone can help with this...
>>>
>>> I have data in Access and it displays well in all reports.  The data I 
>>> am dealing with has part numbers.  These part numbers often are in a 
>>> format similar to:
>>>
>>> 073D11
>>> 073E05
>>> .....
>>>
>>> When I export a product list to excel, it is changing many (a LOT!) of 
>>> the part numbers to scientific format.  Example:
>>>
>>> 073D10 becomes 730000000000 (which appears as 7.3E +11)
>>> 073E11 becomes 7300000000000 (which appears as 7.3E + 12)
>>>
>>> How can I export o Excel and retain the proper number formats 
>>> (effectively as text)?  I have searched through the Microsoft site and 
>>> found it very unhelpful  I found article 
>>> http://support.microsoft.com/kb/214233  and it tells me that Excel does 
>>> this.  Yes, I know it does this.  Under the section "Retaining Number 
>>> Formats When You Import Text Files or Use Parse", it says the following:
>>>
>>> "When you import a text file into Microsoft Excel or use the Text to 
>>> Columns command (located on the Data menu) to place text entries in 
>>> separate columns, Microsoft Excel applies number formats to the data if 
>>> the file contains entries similar to those mentioned in this article."
>>>
>>> This doesn't tell me how to avoid this reformatting.
>>>
>>> Can anyone help?
>>>
>>> Stephen
>>>
>>
>>
>
> 

Ken -

I am going to ust add that new field to the Access report and have it print 
in "white" text.  This way, it is invisible on the report, but WILL appear 
in the Excel output.

Thank you for your help.

Stephen

"Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
news:OZzQs4BiHHA.4064@TK2MSFTNGP02.phx.gbl...
>I assume that you're using a query as the report's Record Source. Add the 
>calculated field I suggested to that query. Then try the export method for 
>the report. You should get that field as an additional column in the excel 
>export. You may or may  not need to put a control on the report that is 
>bound to this calculated field -- quite honestly, I don't use the export to 
>EXCEL option because it doesn't provide me with any benefits/use, so I am 
>not familiar with the nuances of using this feature.
>
> -- 
>
>        Ken Snell
> <MS ACCESS MVP>
>
>
> "Steve" <webmail@thesecongroup.com> wrote in message 
> news:eaFYWtBiHHA.4132@TK2MSFTNGP05.phx.gbl...
>> Ken -
>>
>> The report I am using now is simply a report from Access.  Then I use the 
>> Access command button "Export To Excel" to export the report to an Excel 
>> worksheet.  I am not using any SQL query to "export" the data.  The SQL 
>> Query is sed only to generate an Access report.
>>
>> Is the query you are instruction me to use initiated in "generating the 
>> access report", or "exporting the access report to Excel"?  If it is the 
>> former, then I am confused: the Access Report would display  '073E11 as 
>> the code, right?  If you are referring to the latter, then I am still 
>> confused because I am not using any SQL query to "export" the data.  As 
>> mentioned above, I am using the built-in Access command button to "Export 
>> to Excel".
>>
>> Either way, I am confused.  Please help me with additional explanation?
>>
>> Stephen
>> "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
>> news:OiDbk6$hHHA.4976@TK2MSFTNGP03.phx.gbl...
>>> In the query that is used for the data export, use a calculated field in 
>>> place of the real field, and prepend an ' character to the value:
>>>
>>> SELECT FieldName1, FieldName2,
>>> "'" & FieldNameWithLeadZero AS NewFieldNameWIthLeadZero
>>> FROM TableName;
>>>
>>> EXCEL will interpret the leading ' character to mean that the value is 
>>> text.
>>>
>>> -- 
>>>
>>>        Ken Snell
>>> <MS ACCESS MVP>
>>>
>>> "Steve" <webmail@thesecongroup.com> wrote in message 
>>> news:OHTUUQ%23hHHA.588@TK2MSFTNGP06.phx.gbl...
>>>>I hope someone can help with this...
>>>>
>>>> I have data in Access and it displays well in all reports.  The data I 
>>>> am dealing with has part numbers.  These part numbers often are in a 
>>>> format similar to:
>>>>
>>>> 073D11
>>>> 073E05
>>>> .....
>>>>
>>>> When I export a product list to excel, it is changing many (a LOT!) of 
>>>> the part numbers to scientific format.  Example:
>>>>
>>>> 073D10 becomes 730000000000 (which appears as 7.3E +11)
>>>> 073E11 becomes 7300000000000 (which appears as 7.3E + 12)
>>>>
>>>> How can I export o Excel and retain the proper number formats 
>>>> (effectively as text)?  I have searched through the Microsoft site and 
>>>> found it very unhelpful  I found article 
>>>> http://support.microsoft.com/kb/214233  and it tells me that Excel does 
>>>> this.  Yes, I know it does this.  Under the section "Retaining Number 
>>>> Formats When You Import Text Files or Use Parse", it says the 
>>>> following:
>>>>
>>>> "When you import a text file into Microsoft Excel or use the Text to 
>>>> Columns command (located on the Data menu) to place text entries in 
>>>> separate columns, Microsoft Excel applies number formats to the data if 
>>>> the file contains entries similar to those mentioned in this article."
>>>>
>>>> This doesn't tell me how to avoid this reformatting.
>>>>
>>>> Can anyone help?
>>>>
>>>> Stephen
>>>>
>>>
>>>
>>
>>
>
> 

Hi Steve, have had trouble myself with this for a long while.

Have done as you suggested and the full field is exported but the ' 
characters appears in the excel worksheet and also prints out to paper.

What am I doing wrong

cheers, Garry



"Steve" <webmail@thesecongroup.com> wrote in message 
news:eKahXjCiHHA.4680@TK2MSFTNGP06.phx.gbl...
> Ken -
>
> I am going to ust add that new field to the Access report and have it 
> print in "white" text.  This way, it is invisible on the report, but WILL 
> appear in the Excel output.
>
> Thank you for your help.
>
> Stephen
>
> "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
> news:OZzQs4BiHHA.4064@TK2MSFTNGP02.phx.gbl...
>>I assume that you're using a query as the report's Record Source. Add the 
>>calculated field I suggested to that query. Then try the export method for 
>>the report. You should get that field as an additional column in the excel 
>>export. You may or may  not need to put a control on the report that is 
>>bound to this calculated field -- quite honestly, I don't use the export 
>>to EXCEL option because it doesn't provide me with any benefits/use, so I 
>>am not familiar with the nuances of using this feature.
>>
>> -- 
>>
>>        Ken Snell
>> <MS ACCESS MVP>
>>
>>
>> "Steve" <webmail@thesecongroup.com> wrote in message 
>> news:eaFYWtBiHHA.4132@TK2MSFTNGP05.phx.gbl...
>>> Ken -
>>>
>>> The report I am using now is simply a report from Access.  Then I use 
>>> the Access command button "Export To Excel" to export the report to an 
>>> Excel worksheet.  I am not using any SQL query to "export" the data. 
>>> The SQL Query is sed only to generate an Access report.
>>>
>>> Is the query you are instruction me to use initiated in "generating the 
>>> access report", or "exporting the access report to Excel"?  If it is the 
>>> former, then I am confused: the Access Report would display  '073E11 as 
>>> the code, right?  If you are referring to the latter, then I am still 
>>> confused because I am not using any SQL query to "export" the data.  As 
>>> mentioned above, I am using the built-in Access command button to 
>>> "Export to Excel".
>>>
>>> Either way, I am confused.  Please help me with additional explanation?
>>>
>>> Stephen
>>> "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
>>> news:OiDbk6$hHHA.4976@TK2MSFTNGP03.phx.gbl...
>>>> In the query that is used for the data export, use a calculated field 
>>>> in place of the real field, and prepend an ' character to the value:
>>>>
>>>> SELECT FieldName1, FieldName2,
>>>> "'" & FieldNameWithLeadZero AS NewFieldNameWIthLeadZero
>>>> FROM TableName;
>>>>
>>>> EXCEL will interpret the leading ' character to mean that the value is 
>>>> text.
>>>>
>>>> -- 
>>>>
>>>>        Ken Snell
>>>> <MS ACCESS MVP>
>>>>
>>>> "Steve" <webmail@thesecongroup.com> wrote in message 
>>>> news:OHTUUQ%23hHHA.588@TK2MSFTNGP06.phx.gbl...
>>>>>I hope someone can help with this...
>>>>>
>>>>> I have data in Access and it displays well in all reports.  The data I 
>>>>> am dealing with has part numbers.  These part numbers often are in a 
>>>>> format similar to:
>>>>>
>>>>> 073D11
>>>>> 073E05
>>>>> .....
>>>>>
>>>>> When I export a product list to excel, it is changing many (a LOT!) of 
>>>>> the part numbers to scientific format.  Example:
>>>>>
>>>>> 073D10 becomes 730000000000 (which appears as 7.3E +11)
>>>>> 073E11 becomes 7300000000000 (which appears as 7.3E + 12)
>>>>>
>>>>> How can I export o Excel and retain the proper number formats 
>>>>> (effectively as text)?  I have searched through the Microsoft site and 
>>>>> found it very unhelpful  I found article 
>>>>> http://support.microsoft.com/kb/214233  and it tells me that Excel 
>>>>> does this.  Yes, I know it does this.  Under the section "Retaining 
>>>>> Number Formats When You Import Text Files or Use Parse", it says the 
>>>>> following:
>>>>>
>>>>> "When you import a text file into Microsoft Excel or use the Text to 
>>>>> Columns command (located on the Data menu) to place text entries in 
>>>>> separate columns, Microsoft Excel applies number formats to the data 
>>>>> if the file contains entries similar to those mentioned in this 
>>>>> article."
>>>>>
>>>>> This doesn't tell me how to avoid this reformatting.
>>>>>
>>>>> Can anyone help?
>>>>>
>>>>> Stephen
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>