keeping number formats when exporting from Access to Excel

[Guest]

Data exported from Access to Excel has some phone numbers formatted with the
(-) character and some without. Also some postal codes retain the space and
some do not. How can I retain this formatting?

 

[Roger Carlson]

Export them from a query rather than directly from the table. Use the
Format() function to format them the way you want it. But you need to get
your data in a consistant form first. You can use the Replace function
(Access 2000 or above) to remove all of the dashes. You can do this in one
statement in your query.

SELECT Format(replace([PhoneField],"-",""),"@@@-@@@-@@@@") AS Phone
FROM MyTable;

In the query builder, it looks like this in the Field column:
Phone: Format(replace([PhoneField],"-",""),"@@@-@@@-@@@@")


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jeff Boyce]

One way to accomplish this is to create a query that returns the "raw"
values, then use formatting (in the query) and the CStr() function to coerce
the raw values into the final form.

Then you export the query (actually, Access exports the values found by the
query).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

There is a KB article this. http://support.microsoft.com/kb/823222/en-us
The article was created last year. Does anyone know if they fixed this
problem yet?