Exporting Text with a variable Number of Leading Zeros

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stephen - 26 Feb 2007 18:49 GMT

Greetings!

I am trying to export data that has a variable number of leading zeros. The
export into Excel always drops the zeros which I wish to retain. The data
type is set to text. When I export the report to a "txt" file and then import
into Excel I am able retain the zeros.

Question, is there a way to export this data from a report directly into
Excel and retain the leading zeros?

Thanks for any help you may be able to provide to me.

Signature

Stephen

Reply to this Message

John Nurick - 26 Feb 2007 22:00 GMT

Hi Stephen,

Set up a query that sticks an apostrophe in front of each value, e.g.

'007

These values should transfer to Excel as text.

>Greetings!
>
[quoted text clipped - 7 lines]
>
>Thanks for any help you may be able to provide to me.

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Reply to this Message

Stephen - 27 Feb 2007 14:01 GMT

Thanks John I will try that.

Any idea as to why this is necessary if the data type is set to text?

Thanks,

Stephen

Signature

Stephen

> Hi Stephen,
>
[quoted text clipped - 20 lines]
>
> Please respond in the newsgroup and not by email.

Reply to this Message

John Nurick - 27 Feb 2007 19:45 GMT

Excel cells don't have data types in the same sense as in fields in
database tables. When Excel finds something that looks like a number, it
usually treats it as a number.

>Thanks John I will try that.
>
[quoted text clipped - 3 lines]
>
>Stephen

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Reply to this Message