dissappearing front zeros in zip codes when using report "analyzing it with excel" option

  • Thread starter Thread starter Philip Leduc
  • Start date Start date
P

Philip Leduc

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
 
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...
 
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
 
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
 
Back
Top