when I build a report in Access and use the "analyze it with excel " on the
toolbar to export my data to excel, the front 0 in zip codes dissappears
because excel thinks it is number, even if there are other 9 digit zip-codes
in the same column (thise work fine). Any easy solution for this?

Which version of Access are you using? In 2002, I get Excel Smart Tags
telling me that it's a number stored as text:

ID
Zip

1
32714

2
90211

3
25541

4
32708

6
21218

7
21207

8
32789

9
32789

10
32714

11
90211

12
25541

13
32708

14
21218

15
21207

16
32789

17
01212


--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Philip Leduc" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> when I build a report in Access and use the "analyze it with excel " on
> the toolbar to export my data to excel, the front 0 in zip codes
> dissappears because excel thinks it is number, even if there are other 9
> digit zip-codes in the same column (thise work fine). Any easy solution
> for this?
>

my zip fields are defined as text , I was using Access 2003 , just tried it
in Access 2000, same problem (the file I am using is Access 2000 file
format)
Do not see any smart tags...

"Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Which version of Access are you using? In 2002, I get Excel Smart Tags
> telling me that it's a number stored as text:
>
> ID
> Zip
>
> 1
> 32714
>
> 2
> 90211
>
> 3
> 25541
>
> 4
> 32708
>
> 6
> 21218
>
> 7
> 21207
>
> 8
> 32789
>
> 9
> 32789
>
> 10
> 32714
>
> 11
> 90211
>
> 12
> 25541
>
> 13
> 32708
>
> 14
> 21218
>
> 15
> 21207
>
> 16
> 32789
>
> 17
> 01212
>
>
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
> "Philip Leduc" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> when I build a report in Access and use the "analyze it with excel " on
>> the toolbar to export my data to excel, the front 0 in zip codes
>> dissappears because excel thinks it is number, even if there are other 9
>> digit zip-codes in the same column (thise work fine). Any easy solution
>> for this?
>>
>
>

Try changing the field size to 10 characters. Other than the OS, that is the
only possible difference that I can see. Are you using Win XP SP2?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Philip Leduc" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> my zip fields are defined as text , I was using Access 2003 , just tried
> it in Access 2000, same problem (the file I am using is Access 2000 file
> format)
> Do not see any smart tags...
>
> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Which version of Access are you using? In 2002, I get Excel Smart Tags
>> telling me that it's a number stored as text:
>>
>> ID
>> Zip
>>
>> 1
>> 32714
>>
>> 2
>> 90211
>>
>> 3
>> 25541
>>
>> 4
>> 32708
>>
>> 6
>> 21218
>>
>> 7
>> 21207
>>
>> 8
>> 32789
>>
>> 9
>> 32789
>>
>> 10
>> 32714
>>
>> 11
>> 90211
>>
>> 12
>> 25541
>>
>> 13
>> 32708
>>
>> 14
>> 21218
>>
>> 15
>> 21207
>>
>> 16
>> 32789
>>
>> 17
>> 01212
>>
>>
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.mvps.org/access
>> http://www.accessmvp.com
>>
>> "Philip Leduc" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> when I build a report in Access and use the "analyze it with excel " on
>>> the toolbar to export my data to excel, the front 0 in zip codes
>>> dissappears because excel thinks it is number, even if there are other 9
>>> digit zip-codes in the same column (thise work fine). Any easy solution
>>> for this?
>>>
>>
>>
>
>

Field size is 10 caracters and I am using win XP sp2. It is actually doing
this as well on my machine as well as on my clients...
I found one solution on the web that might work for my client, by selecting
the column and formating the column to special, zip, the front zeros
reappear even if they are not there in the cell.
They even appear in the mailmerge in word

Thanks for your help, though I am still intigued why you can export them and
I am not

"Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Try changing the field size to 10 characters. Other than the OS, that is
> the only possible difference that I can see. Are you using Win XP SP2?
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
> "Philip Leduc" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> my zip fields are defined as text , I was using Access 2003 , just tried
>> it in Access 2000, same problem (the file I am using is Access 2000 file
>> format)
>> Do not see any smart tags...
>>
>> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Which version of Access are you using? In 2002, I get Excel Smart Tags
>>> telling me that it's a number stored as text:
>>>
>>> ID
>>> Zip
>>>
>>> 1
>>> 32714
>>>
>>> 2
>>> 90211
>>>
>>> 3
>>> 25541
>>>
>>> 4
>>> 32708
>>>
>>> 6
>>> 21218
>>>
>>> 7
>>> 21207
>>>
>>> 8
>>> 32789
>>>
>>> 9
>>> 32789
>>>
>>> 10
>>> 32714
>>>
>>> 11
>>> 90211
>>>
>>> 12
>>> 25541
>>>
>>> 13
>>> 32708
>>>
>>> 14
>>> 21218
>>>
>>> 15
>>> 21207
>>>
>>> 16
>>> 32789
>>>
>>> 17
>>> 01212
>>>
>>>
>>> --
>>> Arvin Meyer, MCP, MVP
>>> http://www.datastrat.com
>>> http://www.mvps.org/access
>>> http://www.accessmvp.com
>>>
>>> "Philip Leduc" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> when I build a report in Access and use the "analyze it with excel " on
>>>> the toolbar to export my data to excel, the front 0 in zip codes
>>>> dissappears because excel thinks it is number, even if there are other
>>>> 9 digit zip-codes in the same column (thise work fine). Any easy
>>>> solution for this?
>>>>
>>>
>>>
>>
>>
>
>

I have had this trouble in every version of access / excel. A query will go
to excel as text while a report based on the same query will go as number
and drop the zeros. You could try sending the query to excel rather than
the report. Or add a dummy line by using a union query like this:

SELECT "00A" as ZipCode,""as town,"" as households,"" as zone FROM
tblZipCodes UNION SELECT tblZipCodes.Zip AS ZipCode, tblZipCodes.Town,
tblZipCodes.HouseHolds, tblZipCodes.Zone
FROM tblZipCodes

Apparently, the export only looks at the first record to determine if it's
text or number so you need to make sure your dummy come first. I hate this
solution, but it's all I have found.

Lastly, you can try an export specification. And use CSV which can be
opened in excel.

HTH,
Josh

"Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Try changing the field size to 10 characters. Other than the OS, that is
> the only possible difference that I can see. Are you using Win XP SP2?
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
> "Philip Leduc" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> my zip fields are defined as text , I was using Access 2003 , just tried
>> it in Access 2000, same problem (the file I am using is Access 2000 file
>> format)
>> Do not see any smart tags...
>>
>> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Which version of Access are you using? In 2002, I get Excel Smart Tags
>>> telling me that it's a number stored as text:
>>>
>>> ID
>>> Zip
>>>
>>> 1
>>> 32714
>>>
>>> 2
>>> 90211
>>>
>>> 3
>>> 25541
>>>
>>> 4
>>> 32708
>>>
>>> 6
>>> 21218
>>>
>>> 7
>>> 21207
>>>
>>> 8
>>> 32789
>>>
>>> 9
>>> 32789
>>>
>>> 10
>>> 32714
>>>
>>> 11
>>> 90211
>>>
>>> 12
>>> 25541
>>>
>>> 13
>>> 32708
>>>
>>> 14
>>> 21218
>>>
>>> 15
>>> 21207
>>>
>>> 16
>>> 32789
>>>
>>> 17
>>> 01212
>>>
>>>
>>> --
>>> Arvin Meyer, MCP, MVP
>>> http://www.datastrat.com
>>> http://www.mvps.org/access
>>> http://www.accessmvp.com
>>>
>>> "Philip Leduc" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> when I build a report in Access and use the "analyze it with excel " on
>>>> the toolbar to export my data to excel, the front 0 in zip codes
>>>> dissappears because excel thinks it is number, even if there are other
>>>> 9 digit zip-codes in the same column (thise work fine). Any easy
>>>> solution for this?
>>>>
>>>
>>>
>>
>>
>
>

I just tried it in Access 2003 and got the same result as in 2002. The only
possible difference that I can see is that I am exporting directly from the
query, whereas you said you were using the report.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Philip Leduc" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Field size is 10 caracters and I am using win XP sp2. It is actually doing
> this as well on my machine as well as on my clients...
> I found one solution on the web that might work for my client, by
> selecting the column and formating the column to special, zip, the front
> zeros reappear even if they are not there in the cell.
> They even appear in the mailmerge in word
>
> Thanks for your help, though I am still intigued why you can export them
> and I am not
>
> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Try changing the field size to 10 characters. Other than the OS, that is
>> the only possible difference that I can see. Are you using Win XP SP2?
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.mvps.org/access
>> http://www.accessmvp.com
>>
>> "Philip Leduc" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> my zip fields are defined as text , I was using Access 2003 , just tried
>>> it in Access 2000, same problem (the file I am using is Access 2000 file
>>> format)
>>> Do not see any smart tags...
>>>
>>> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> Which version of Access are you using? In 2002, I get Excel Smart Tags
>>>> telling me that it's a number stored as text:
>>>>
>>>> ID
>>>> Zip
>>>>
>>>> 1
>>>> 32714
>>>>
>>>> 2
>>>> 90211
>>>>
>>>> 3
>>>> 25541
>>>>
>>>> 4
>>>> 32708
>>>>
>>>> 6
>>>> 21218
>>>>
>>>> 7
>>>> 21207
>>>>
>>>> 8
>>>> 32789
>>>>
>>>> 9
>>>> 32789
>>>>
>>>> 10
>>>> 32714
>>>>
>>>> 11
>>>> 90211
>>>>
>>>> 12
>>>> 25541
>>>>
>>>> 13
>>>> 32708
>>>>
>>>> 14
>>>> 21218
>>>>
>>>> 15
>>>> 21207
>>>>
>>>> 16
>>>> 32789
>>>>
>>>> 17
>>>> 01212
>>>>
>>>>
>>>> --
>>>> Arvin Meyer, MCP, MVP
>>>> http://www.datastrat.com
>>>> http://www.mvps.org/access
>>>> http://www.accessmvp.com
>>>>
>>>> "Philip Leduc" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> when I build a report in Access and use the "analyze it with excel "
>>>>> on the toolbar to export my data to excel, the front 0 in zip codes
>>>>> dissappears because excel thinks it is number, even if there are other
>>>>> 9 digit zip-codes in the same column (thise work fine). Any easy
>>>>> solution for this?
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>