Exporting to Excel (97)

Topic

New Reply

I’m trying to export an Access 97 report into Excel 97 and have run into a problem. A couple of columns in the report contains rankings like 4-, 2+, 3, etc. When I try and export this report into an Excel file, all of the “+” signs in these columns are dropped, while the “-” signs come through without a problem.

I recognize this is some sort of formatting issue, but no matter what I try, I can’t seem to fix this.

Any ideas?

Thanks

Reply | Quote

Replies

Try formatting the Column Headings in Excel as text fields, rather than numeric.

Reply | Quote

I tried that but it didn’t work. Somehow I have to have Access create the column as a text field in Excel before it exports the file, if you know what I mean…

Reply | Quote

Try exporting a query, rather than a report.

Reply | Quote

I tried that and it works, but I lose some data calculations that I’ve incorporated into the report that isn’t in the query.

Reply | Quote

You could try appending a “hard space” Chr(160) to the result in the query so that Excel will treat it as text

NewString: Chr(160) & [OldString]

HTH

Peter

Reply | Quote

That sounds promising but I’m fairly new at Access so I not exactly sure exactly where to do this in the query or report

Thanks

Reply | Quote

Do it in the query. Assuming that the field with the ranking is called strRank then:-
Go to the column that has the ranking and change it from StrRank to StrRankPad:Chr(160) & [strRank] . Close and save the Query
Open the report and go to your StrRank text box and in the Properties Sheet change its Control Source from strRank to strRankPad

Hope that makes sense

You could probaly put the Space after the result rather than first if that will look better in the report.

Peter

Reply | Quote

That made sense and I no longer lose the “+” signs when I export to Excel. Thanks!

Reply | Quote

I tried to specify the column as text in Excel, but it doesn’t make a difference. The custom format is a good idea, except these are credit rankings and if I do that, it places a “+” in front of all of the positive numbers where I only need it on certain ones. i.e. 3 and 3+ would both be valid entries in the column.

Reply | Quote