Link Access to Excel export back to excel

[Steve54]

I’m using Microsoft XP Pro. With Excel and Access 2003 I’ve link and excel
spread sheet to access sorted and filtered the data. In my header roll in
Excel, (format cell text) I have one column with SERID with 0001, 0002, 0003.
And export to Access and run a report and I get the 0001, 0002, 0003, work
fine, but when I export back to excel I lose the 000 I get only 1, 2, 3.Can
any one provide any assistance how to get 0001, to show back up in my Excel
spread sheet, Thanks


Excel
SERID
0001
0002
0003

Access
SERID
0001
0002
000

Excel
SERID
1
2
3

 

[Ken Snell [MVP]]

Export a query that is based on your table. In place of the SERID field in
the query, use a calculated field with an expression like this:

"'" & SERID AS SERID_1

Note that this will cause SERID_1 to be the "column header" in the EXCEL
file to where you export the data. If you need to have SERID as the column
header, than you'll need to use two queries:

qryOne
-------
SELECT FieldName1, SERID AS SERID_1, FieldName3, FieldName4
FROM TableName;


qryTwo (the one to export):
---------------------------
SELECT FieldName1, "'" & SERID_1 AS SERID, FieldName3, FieldName4
FROM qryOne;

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Prepending an apostrophe to the string will tell

 

[Steve54]

Ken
Thanks for the help, I've been trying to work this out,This works great if I
export to excel from a query, what I'm trying to do after I run the report
thats when I export and lose the 000.I can change the Excel header name to
what ever I wish.
Thanks
Steve

 

[Ken Snell [MVP]]

Exporting from a report is not quite the same as exporting from a query, as
you're seeing. Is it not possible to export a query that uses the report's
RecordSource query?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/