Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting to Excel (97)

    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

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Exporting to Excel (97)

    Try formatting the Column Headings in Excel as text fields, rather than numeric.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to Excel (97)

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

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to Excel (97)

    Try exporting a query, rather than a report.

  5. #5
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to Excel (97)

    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.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to Excel (97)

    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

  7. #7
    eLar
    Guest

    Re: Exporting to Excel (97)

    Try exporting the report to text format, then import it into Excel, making sure to mark that column as text when you do the import. Alternatively, with what you have simply give that column a custom number format that displays a "+" with positive numbers or a "-" with negative numbers.

  8. #8
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to Excel (97)

    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

  9. #9
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to Excel (97)

    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.

  10. #10
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to Excel (97)

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

    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

  11. #11
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to Excel (97)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •