Results 1 to 6 of 6
  1. #1
    ShostyFan is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    76

    report export to excel

    I have a report that sums a dollar amount by Account. The control source formula for the Account name also tells me how many transactions were compiled for the total.


    =[Account] & " (" & Count(*) & " " & IIf(Count(*)=1,"detail record") & ")"

    This is all good.

    When I export this report to Excel using the external data menu option, the field "Account" says "Sum Of SumOfPayment" for each account.

    Any help on resolving this so the report exports to excel the same way the report reads in Access would be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why export to Excel? Complex reports often don't export nicely to Excel.

    What happens if you put the Count() function in its own textbox (maybe not visible on report) and then expression references that textbox?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ShostyFan is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    76
    EOY reports are put together in Excel for accountants.
    The users here are not excel proficient so I have access export the raw data and then a separate report that presents the data in a pretty format, the user then copies and pasts everything from access to excel, this is a tried and true method that takes very little extra time and allows non excel users be able to communicate with accountants.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, so if raw data is in Excel, why the pretty report to Excel? Maybe report to PDF?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ShostyFan is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    76
    To answer the question "why the pretty report in excel"; it is because that is what our accountant wants.
    In case someone else stumbles on this thread I figured out the answer:
    instead of the report supplying the calculations; have the query do the work, then make the report from the query results.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So it was the calc they wanted, not the 'pretty' report? Glad you figured out a solution.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. export report to excel
    By AMRI YULIANDI in forum Reports
    Replies: 4
    Last Post: 12-11-2014, 07:10 AM
  2. export report to excel
    By sdel_nevo in forum Import/Export Data
    Replies: 4
    Last Post: 07-10-2013, 09:19 AM
  3. Simple report export to excel
    By crombiecrunch in forum Reports
    Replies: 2
    Last Post: 05-30-2013, 09:10 AM
  4. Report export to Excel with format
    By mrswarrick in forum Access
    Replies: 3
    Last Post: 02-29-2012, 03:10 PM
  5. Report Export to Excel
    By BLD21 in forum Import/Export Data
    Replies: 1
    Last Post: 05-06-2011, 10:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums